MySQL explain - require index suggestions / feedback [closed]

Question

I have a database containing around 5million rows and am having issues with a query taking a long time (over a minute). I was hoping this information would be enough for someone to give me some suggestions, if I need to post more information just let me know.

Thank you in advance for any advice

EXPLAIN SELECT count( * ) AS count
FROM vtiger_time
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_time.timeid
INNER JOIN vtiger_crmentityrel ON ( vtiger_crmentityrel.relcrmid = vtiger_crmentity.crmid
OR vtiger_crmentityrel.crmid = vtiger_crmentity.crmid )
LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid
WHERE vtiger_crmentity.deleted =0
AND (
    vtiger_crmentityrel.crmid =211294
    OR vtiger_crmentityrel.relcrmid =211294
)


+----+-------------+---------------------+-------------+-------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
| id | select_type | table               | type        | possible_keys                                   | key            | key_len | ref                            | rows  | Extra                                    |
+----+-------------+---------------------+-------------+-------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
|  1 | SIMPLE      | vtiger_crmentityrel | index_merge | crmid,relcrmid                                  | crmid,relcrmid | 4,4     | NULL                           |  5881 | Using union(crmid,relcrmid); Using where | 
|  1 | SIMPLE      | vtiger_crmentity    | ref         | PRIMARY,deleted,deleted_2,crmentity_multi_index | deleted_2      | 4       | const                          | 84424 | Using where; Using index                 | 
|  1 | SIMPLE      | vtiger_users        | eq_ref      | PRIMARY                                         | PRIMARY        | 4       | crm.vtiger_crmentity.smownerid |     1 | Using index                              | 
|  1 | SIMPLE      | vtiger_groups       | eq_ref      | PRIMARY                                         | PRIMARY        | 4       | crm.vtiger_crmentity.smownerid |     1 | Using index                              | 
|  1 | SIMPLE      | vtiger_time      | eq_ref      | PRIMARY,timeid                               | PRIMARY        | 4       | crm.vtiger_crmentity.crmid     |     1 | Using index                              | 
+----+-------------+---------------------+-------------+-------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+

Additionally these are my currently set indexes

+------------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_crmentity |          0 | PRIMARY                    |            1 | crmid       | A         |      755968 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          0 | crmid                      |            1 | crmid       | A         |      755968 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_IDX0             |            1 | smcreatorid | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_IDX1             |            1 | smownerid   | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_IDX2             |            1 | modifiedby  | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | deleted                    |            1 | deleted     | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | deleted                    |            2 | smownerid   | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | smownerid                  |            1 | smownerid   | A         |         199 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | smownerid                  |            2 | deleted     | A         |         199 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | deleted_2                  |            1 | deleted     | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | deleted_2                  |            2 | smownerid   | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | smownerid_2                |            1 | smownerid   | A         |         385 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | smownerid_2                |            2 | deleted     | A         |         758 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crm_ownerid_del_setype_idx |            1 | smownerid   | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crm_ownerid_del_setype_idx |            2 | deleted     | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crm_ownerid_del_setype_idx |            3 | setype      | A         |         613 |     NULL | NULL   | YES  | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_multi_index      |            1 | crmid       | A         |      755968 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_multi_index      |            2 | smownerid   | A         |      755968 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_multi_index      |            3 | deleted     | A         |      755968 |     NULL | NULL   |      | BTREE      |         | 
+------------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table               | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_crmentityrel |          1 | crmid    |            1 | crmid       | A         |      223960 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentityrel |          1 | relcrmid |            1 | relcrmid    | A         |       12442 |     NULL | NULL   |      | BTREE      |         | 
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+--------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_users |          0 | PRIMARY       |            1 | id            | A         |          39 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_users |          1 | idx_user_name |            1 | user_name     | A         |          39 |     NULL | NULL   | YES  | BTREE      |         | 
| vtiger_users |          1 | user_password |            1 | user_password | A         |          39 |     NULL | NULL   | YES  | BTREE      |         | 
+--------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+

+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_groups |          0 | PRIMARY             |            1 | groupid     | A         |           5 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_groups |          1 | groupname           |            1 | groupname   | A         |           5 |     NULL | NULL   | YES  | BTREE      |         | 
| vtiger_groups |          1 | idx_groups_123group |            1 | groupname   | A         |           5 |     NULL | NULL   | YES  | BTREE      |         | 
+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_time |          0 | PRIMARY     |            1 | timeid   | A         |      591772 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_time |          0 | timeid   |            1 | timeid   | A         |      591772 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_time |          1 | relatedto   |            1 | relatedto   | A         |        1405 |     NULL | NULL   | YES  | BTREE      |         | 
| vtiger_time |          1 | date_start  |            1 | date_start  | A         |        7129 |     NULL | NULL   | YES  | BTREE      |         | 
| vtiger_time |          1 | relatedto_2 |            1 | relatedto   | A         |        3269 |     NULL | NULL   | YES  | BTREE      |         | 
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_timecf |          0 | PRIMARY     |            1 | timeid   | A         |      591324 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_timecf |          0 | timeid   |            1 | timeid   | A         |      591324 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_timecf |          1 | timeid_2 |            1 | timeid   | A         |      591324 |     NULL | NULL   |      | BTREE      |         | 
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

After adding the following index

ALTER TABLE vtiger_crmentity ADD INDEX TMP_deletion_smownerid_crmid (smownerid, deleted,crmid); 

The new explain:

+----+-------------+---------------------+-------------+--------------------------------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
| id | select_type | table               | type        | possible_keys                                                            | key            | key_len | ref                            | rows  | Extra                                    |
+----+-------------+---------------------+-------------+--------------------------------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
|  1 | SIMPLE      | vtiger_crmentityrel | index_merge | crmid,relcrmid                                                           | crmid,relcrmid | 4,4     | NULL                           |  5891 | Using union(crmid,relcrmid); Using where | 
|  1 | SIMPLE      | vtiger_crmentity    | ref         | PRIMARY,crmid,deleted,deleted_2,crmentity_multi_index,_deletion_crmid | deleted        | 4       | const                          | 84424 | Using where; Using index                 | 
|  1 | SIMPLE      | vtiger_users        | eq_ref      | PRIMARY                                                                  | PRIMARY        | 4       | crm.vtiger_crmentity.smownerid |     1 | Using index                              | 
|  1 | SIMPLE      | vtiger_groups       | eq_ref      | PRIMARY                                                                  | PRIMARY        | 4       | crm.vtiger_crmentity.smownerid |     1 | Using index                              | 
|  1 | SIMPLE      | vtiger_time      | eq_ref      | PRIMARY,timeid                                                        | PRIMARY        | 4       | crm.vtiger_crmentity.crmid     |     1 | Using index                              | 
+----+-------------+---------------------+-------------+--------------------------------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+

Answer 1

Rewriting the query would help, but since you cannot do that, you may benefit from a composite index on vtiger_crmentity.crmid and vtiger_crmentity.deleted. But since it isn't using one of the the index you currently have on vtiger_crmentity.crmid, it might not use that new one. If it doesn't, try adding vtiger_crmentity.crmid to the vtiger_crmentity.deleted index. Since it is already using the index on vtiger_crmentity.deleted, this will make the index a covering index so the query will not need to read from the table.

As I mentioned in my comment, there are a lot of duplicate indexes. The won't affect the query for this question, but they will slow down inserts and increase table size. The following indexes are redundant:

vtiger_crmentity.crmid
vtiger_crmentity.smownerid
vtiger_crmentity.smownerid_2
vtiger_crmentity.deleted_2

vtiger_groups.idx_groups_123group

vtiger_time.timeid
vtiger_time.relatedto_2

vtiger_timecf.timeid
vtiger_timcfe.timeid_2

You also shouldn't need the index on the user password column since you shouldn't be looking for users by password.