Help with a custom index

[stevec]

Hi,

Just wondering if any mysql gurus can help with an index on the emails table. Ours is huge and emails listview can take a good 20 seconds. Slow query logging shows the query:

SELECT DISTINCT emails.*, users.user_name as assigned_user_name  FROM emails  LEFT JOIN teams ON emails.team_id=teams.id LEFT JOIN users ON emails.assigned_user_id=users.id WHERE  emails.deleted=0  ORDER BY date_sent DESC LIMIT 60,21;

using mysql EXPLAIN it gives:

+----+-------------+--------+--------+---------------+---------+---------+----------------------------------+--------+----------------------------------------------+
| id | select_type | table  | type   | possible_keys | key     | key_len | ref                              | rows   | Extra                                        |
+----+-------------+--------+--------+---------------+---------+---------+----------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | emails | ALL    | NULL          | NULL    | NULL    | NULL                             | 477583 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | teams  | eq_ref | PRIMARY       | PRIMARY | 108     | sugarcrm.emails.team_id          |      1 | Using index                                  |
|  1 | SIMPLE      | users  | eq_ref | PRIMARY       | PRIMARY | 108     | sugarcrm.emails.assigned_user_id |      1 |                                              |
+----+-------------+--------+--------+---------------+---------+---------+----------------------------------+--------+----------------------------------------------+

and current indicies are:

+--------+------------+-----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| emails |          0 | PRIMARY               |            1 | id               | A         |      479207 |     NULL | NULL   |      | BTREE      | NULL    |
| emails |          1 | idx_email_name        |            1 | name             | A         |      479207 |     NULL | NULL   | YES  | BTREE      | NULL    |
| emails |          1 | idx_email_parent_id   |            1 | parent_id        | A         |       28188 |     NULL | NULL   | YES  | BTREE      | NULL    |
| emails |          1 | idx_email_team_status |            1 | team_id          | A         |          18 |     NULL | NULL   | YES  | BTREE      | NULL    |
| emails |          1 | idx_email_team_status |            2 | status           | A         |          18 |     NULL | NULL   | YES  | BTREE      | NULL    |
| emails |          1 | idx_email_assigned    |            1 | assigned_user_id | A         |          18 |     NULL | NULL   | YES  | BTREE      | NULL    |
| emails |          1 | idx_email_assigned    |            2 | type             | A         |          18 |     NULL | NULL   | YES  | BTREE      | NULL    |
| emails |          1 | idx_email_assigned    |            3 | status           | A         |          18 |     NULL | NULL   | YES  | BTREE      | NULL    |
| emails |          1 | idx_emails_date_cstm  |            1 | date_modified    | A         |      479207 |     NULL | NULL   |      | BTREE      | NULL    |
| emails |          1 | idx_message_id        |            1 | message_id       | A         |      479207 |     NULL | NULL   | YES  | BTREE      | NULL    |
+--------+------------+-----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

Any thoughts on what index(es) to add that would help with the emails table?

Thanks

[andopes]

Hi Stevec

Try add new indexes:
date_start, time_start, parent_type, mailbox_id, date_sent

Remember adding new indexes will cause more memory usage.

Cheers

[stevec]

Will do - but there is no date_start and time_start fields in emails.

Thanks

[stevec]

I added all the other indices but the main query is still using temporary tables and file sorts. maybe it's the size of the result set that's forcing it to disc?