"Most affected devices" query too slow (fix included)

Hi:

I have a database with around 9000 reports, and I noticed over time that the home screen took more and more time to load. After some weeks without entering MAB-LAB, I have not been able to view the home page. The query has stayed for more than 20 minutes in the "Copying to tmp table" state.

I have been investigating the problem and the query seems to originate from the "Most affected devices" box. The query is as follows:

SELECT phone_model, brand, product, COUNT(DISTINCT issue_id) distinct_count, COUNT(*) count FROM mabl_issues LEFT JOIN mabl_reports ON report_issue=issue_id GROUP BY CONCAT(phone_model, brand, product) ORDER BY distinct_count DESC, count DESC LIMIT 5;

My current MySQL process list:

mysql> show full processlist;
+---------+--------+-----------+-------------------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id      | User   | Host      | db                | Command | Time | State                | Info                                                                                                                                                                                                                                                        |
+---------+--------+-----------+-------------------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1273133 | root   | localhost | mablab_nextbusbcn | Query   |    0 | NULL                 | show full processlist                                                                                                                                                                                                                                       |
| 1273992 | mablab | localhost | mablab_nextbusbcn | Query   | 1184 | Copying to tmp table | SELECT phone_model, brand, product, COUNT(DISTINCT issue_id) distinct_count, COUNT(*) count FROM mabl_issues LEFT JOIN mabl_reports ON report_issue=issue_id GROUP BY CONCAT(phone_model, brand, product) ORDER BY distinct_count DESC, count DESC LIMIT 5; |
+---------+--------+-----------+-------------------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The query has the following plan:

mysql> explain SELECT phone_model, brand, product, COUNT(DISTINCT issue_id) distinct_count, COUNT(*) count FROM mabl_issues LEFT JOIN mabl_reports ON report_issue=issue_id GROUP BY CONCAT(phone_model, brand, product) ORDER BY distinct_count DESC, count DESC LIMIT 5;
+----+-------------+--------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | mabl_issues  | index | NULL          | PRIMARY | 4       | NULL | 5619 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | mabl_reports | ALL   | NULL          | NULL    | NULL    | NULL | 8998 |                                              |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
2 rows in set (0.00 sec)

I have managed to solve the problem by creating an index on the report_issue field in the mabl_reports table:

CREATE INDEX idx_report_issue ON mabl_reports (report_issue);

After that the explain changes to:

mysql> explain SELECT phone_model, brand, product, COUNT(DISTINCT issue_id) distinct_count, COUNT(*) count FROM mabl_issues LEFT JOIN mabl_reports ON report_issue=issue_id GROUP BY CONCAT(phone_model, brand, product) ORDER BY distinct_count DESC, count DESC LIMIT 5;
+----+-------------+--------------+-------+------------------+------------------+---------+----------------------------------------+------+----------------------------------------------+
| id | select_type | table        | type  | possible_keys    | key              | key_len | ref                                    | rows | Extra                                        |
+----+-------------+--------------+-------+------------------+------------------+---------+----------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | mabl_issues  | index | NULL             | PRIMARY          | 4       | NULL                                   | 5619 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | mabl_reports | ref   | idx_report_issue | idx_report_issue | 4       | mablab_nextbusbcn.mabl_issues.issue_id |    2 |                                              |
+----+-------------+--------------+-------+------------------+------------------+---------+----------------------------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

The query now executes almost instantly.

I suggest adding this to the default MAB-LAB installation so it speeds up queries for people with lots of reports.

Medialoha / MAB-LAB

"Most affected devices" query too slow (fix included) #68