speed up a join selection from multiple tables
Hi everyone,
I do not have knowledge in MySQL but have to tune up a query from our institution database that takes like more than 10 sec to load. The query is a select join table from multiple as follow:
Code:
mysql> SELECT packet_callerid.*,questions.message,questions.result,transaction_status.status,transaction_status.update_at,group_packets.name as group_name,packets.name as packet_name from packet_callerid,questions,transaction_status,group_packets,packets where transaction_status.group_id = group_packets.group_id and packets.packet_id = packet_callerid.packet_id and packet_callerid.question_id = questions.question_id and transaction_status.transaction_id = packet_callerid.transaction_id and packet_callerid.createdate>='2014-10-17 00:00:00' AND packet_callerid.createdate<='2014-10-17 23:59:59' order by id desc;
The above takes 13sec to load on our webpage, and if running from mysql console, it even takes longer. Using explain I got:
Code:
mysql> EXPLAIN SELECT packet_callerid.*,questions.message,questions.result,transaction_status.status,transaction_status.update_at,group_packets.name as group_name,packets.name as packet_name from packet_callerid,questions,transaction_status,group_packets,packets where transaction_status.group_id = group_packets.group_id and packets.packet_id = packet_callerid.packet_id and packet_callerid.question_id = questions.question_id and transaction_status.transaction_id = packet_callerid.transaction_id and packet_callerid.createdate>='2014-10-17 00:00:00' AND packet_callerid.createdate<='2014-10-17 23:59:59' order by id desc;
+----+-------------+--------------------+--------+-------------------+---------+---------+-------------------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+--------+-------------------+---------+---------+-------------------------------------------+-------+---------------------------------+
| 1 | SIMPLE | transaction_status | ALL | NULL | NULL | NULL | NULL | 58457 | Using temporary; Using filesort |
| 1 | SIMPLE | group_packets | eq_ref | PRIMARY,packet_id | PRIMARY | 4 | smsgw_tn-mmom.transaction_status.group_id | 1 | |
| 1 | SIMPLE | packet_callerid | ALL | NULL | NULL | NULL | NULL | 59155 | Using where; Using join buffer |
| 1 | SIMPLE | questions | eq_ref | PRIMARY,id | PRIMARY | 4 | smsgw_tn-mmom.packet_callerid.question_id | 1 | |
| 1 | SIMPLE | packets | eq_ref | PRIMARY,packet_id | PRIMARY | 4 | smsgw_tn-mmom.packet_callerid.packet_id | 1 | |
+----+-------------+--------------------+--------+-------------------+---------+---------+-------------------------------------------+-------+---------------------------------+
5 rows in set (0.00 sec)
Anyone has any advice how to reduce the time that the query loads?
Thanks in advance,
D.
