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.