Optimize Query with multiple joins
Hi all, I've been working on this query for some days. I've already improved its performance by a lot, but I'm no specialist and I guess there are still improvements to be done. The queries look like the following one, but they may be shorter or larger. All of the tables use MyISAM and inserts are rare.
SELECT distinct d.id, //several fields from several tables
FROM dest INNER JOIN act_r ON act_r.destId=dest.id INNER JOIN dest_c dc ON dc.destId_B=dest.id INNER JOIN act_r a_0 ON dest.id=a_0.destId INNER JOIN act_r a_1 ON dest.id=a_1.destId INNER JOIN act_t t_0 ON dest.id=t_0.destId INNER JOIN act_t t_1 ON dest.id=t_1.destId INNER JOIN act_t t_2 ON dest.id=t_2.destId
WHERE dest.r=1 and dest.i=1 and dest.min IN (1,2,3,4,5,6,7,8) and dest.max >=21
and dc.destId_A=3000 and dc.value>=35 and dc.points<=10500
and act_r.actId=21 and act_r.value >= 30
and a_0.actId=2 and a_0.value >= 20
and t_0.act_t= 4 and t_0.value_average >= 40
//similar conditions for the rest of the tables joined
limit 40;
I've created indexes and denormalized the schema. At this moment, this is the EXPLAIN EXTENDED result. I hope you can read it properly...
+----+-------------+--------------+--------+-----------------------------------------------------------------------------+---------------+---------+--------------------------------------------------+------+----------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+--------+-----------------------------------------------------------------------------+---------------+---------+--------------------------------------------------+------+----------+-------------------------------------------+
| 1 | SIMPLE | t_1 | range | PRIMARY,id_act_type | id_act_type | 5 | NULL | 11 | 81.82 | Using where; Using index; Using temporary |
| 1 | SIMPLE | t_2 | eq_ref | PRIMARY,other....... | PRIMARY | 5 | const,db.t_1.destId | 1 | 100.00 | Using where |
| 1 | SIMPLE | a_0 | eq_ref | PRIMARY,other....... | PRIMARY | 3 | db.t_scores_2.destId,const | 1 | 100.00 | Using where |
| 1 | SIMPLE | a_1 | eq_ref | PRIMARY,other....... | PRIMARY | 3 | db.t_scores_2.destId,const | 1 | 100.00 | Using where |
| 1 | SIMPLE | dest | eq_ref | PRIMARY,other....... | PRIMARY | 2 | db.a_1.destId | 1 | 100.00 | Using where |
| 1 | SIMPLE | t_0 | eq_ref | PRIMARY,other....... | PRIMARY | 5 | const,db.dest.id | 1 | 100.00 | Using where |
| 1 | SIMPLE | dc | eq_ref | PRIMARY,other....... | PRIMARY | 4 | const,db.dest.id | 1 | 100.00 | Using where |
| 1 | SIMPLE | act_r | eq_ref | PRIMARY,other....... | PRIMARY | 3 | db.t_0.destId,const | 1 | 100.00 | Using where |
+----+-------------+--------------+--------+-----------------------------------------------------------------------------+---------------+---------+--------------------------------------------------+------+----------+-------------------------------------------+
The act_r, a_0, a_1 are the same table with ~24K rows
The t_1,t_2 are the same table with ~6K rows
The dc table has ~1.1M rows
the dest table has ~2.5K rows
Under normal circumstances the query time is around 0.002 secs, but under heavy load the time increases. This is a line registered in the slow query log. The rows_sent = 0 is ok with us.
# Query_time: 1.434681 Lock_time: 0.000115 Rows_sent: 0 Rows_examined: 39
When profiling under normal circumstances, it spends most of its time on "statistics" and "copying to tmp table".
Btw, I think the "distinct" is not needed. When I remove it the results are the same and the "using temporary" dissapears from the 1st line of the explain, but the benchmarks times are worse without it. Don't know if this makes any sense to you... maybe my benchmarks are simply wrong.
At this moment the RAM memory size is 8GB, but it may be decreased to 4GB. I'm not responsible for the server/MySQL configuration, but I can do suggestions to the system administrators. If you want to know the value of a MySQL variable, just ask.
Thanks in advance for your advices