I have got a quite complex query with many joins which runs very well without ordering. But as soon as i try to order by any of my fields it is executing extremely slow and takes about 30 seconds to complete.
Here's the query:
SELECT SQL_NO_CACHE *
FROM et_order
INNER JOIN et_order_type ON et_order.type_id = et_order_type.id
INNER JOIN et_order_data ON et_order.id = et_order_data.order_id
INNER JOIN et_user et_user_consultant ON et_order.user_id_consulting = et_user_consultant.id
INNER JOIN et_customer ON et_order.customer_id = et_customer.id
INNER JOIN et_appointment ON et_order.appointment_id = et_appointment.id
INNER JOIN et_order_status order_status ON et_order.order_status_id = order_status.id
INNER JOIN et_status glass_r_status ON et_order_data.status_id_glass_r = glass_r_status.id
INNER JOIN et_status glass_l_status ON et_order_data.status_id_glass_l = glass_l_status.id
ORDER BY et_order.id DESC
LIMIT 50
The original query is even bigger and has various WHERE operations as well, but even the base query without any condition is unreasonably slow. When I remove the ORDER BY et_order.id DESC the query takes about 0.01 secs to fetch.
In my original query, I select every single field I need separately - just changed it to 'SELECT *' now for better readability of the statement.
Explain Select gives the following result:
+----+-------------+--------------------+--------+-------------------------------------------------------------------------------+-------------+---------+-----------------------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+--------+-------------------------------------------------------------------------------+-------------+---------+-----------------------------------------+-------+---------------------------------+ | 1 | SIMPLE | et_customer | ALL | PRIMARY | NULL | NULL | NULL | 59750 | Using temporary; Using filesort | | 1 | SIMPLE | et_order | ref | PRIMARY,customer_id,appointment_id,user_id_consulting,order_status_id,type_id | customer_id | 4 | eyetool.et_customer.id | 1 | | | 1 | SIMPLE | et_user_consultant | eq_ref | PRIMARY | PRIMARY | 4 | eyetool.et_order.user_id_consulting | 1 | | | 1 | SIMPLE | et_appointment | ref | PRIMARY | PRIMARY | 8 | eyetool.et_order.appointment_id | 1 | | | 1 | SIMPLE | et_order_data | ref | status_id_glass_l,status_id_glass_r,order_id | order_id | 5 | eyetool.et_order.id | 1 | Using where | | 1 | SIMPLE | et_order_type | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where; Using join buffer | | 1 | SIMPLE | glass_l_status | eq_ref | PRIMARY | PRIMARY | 4 | eyetool.et_order_data.status_id_glass_l | 1 | | | 1 | SIMPLE | order_status | eq_ref | PRIMARY,id | PRIMARY | 4 | eyetool.et_order.order_status_id | 1 | | | 1 | SIMPLE | glass_r_status | eq_ref | PRIMARY | PRIMARY | 4 | eyetool.et_order_data.status_id_glass_r | 1 | | +----+-------------+--------------------+--------+-------------------------------------------------------------------------------+-------------+---------+-----------------------------------------+-------+---------------------------------+ 9 rows in set (0.00 sec)
What I don't really understand is why explain select says it does not use any key for et_order_type. Maybe because it is not needed since there are only 4 rows in it!?
But there is an index on type_id in et_order: KEY type_id (type_id)
I've added a (single) INDEX for every key I am using for joining and ordering. Could this be the problem? Do I need to create combined indexes?
The Table contains about 200.000 datasets in et_order and et_order_data, 60.000 in et_customer, 150.000 in et_apointments. Other contents are negligible.
When i just join et_order_data and et_order_type, it also takes very long and explain select still says key NULL for et_order_type:
EXPLAIN SELECT SQL_NO_CACHE * FROM et_order INNER JOIN et_order_type ON et_order.type_id = et_order_type.id INNER JOIN et_order_data ON et_order.id = et_order_data.order_id ORDER BY et_order.id DESC LIMIT 50 +----+-------------+---------------+------+-----------------+----------+---------+---------------------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+-----------------+----------+---------+---------------------+--------+---------------------------------+ | 1 | SIMPLE | et_order | ALL | PRIMARY,type_id | NULL | NULL | NULL | 162007 | Using temporary; Using filesort | | 1 | SIMPLE | et_order_data | ref | order_id | order_id | 5 | eyetool.et_order.id | 1 | Using where | | 1 | SIMPLE | et_order_type | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where; Using join buffer | +----+-------------+---------------+------+-----------------+----------+---------+---------------------+--------+---------------------------------+
The Table structure for et_order and et_order_type can be reviewed here: http://pastebin.com/PED6Edyx
Any tips to optimize my query?
I tried ordering in a subquery like:
SELECT SQL_NO_CACHE *
FROM (SELECT * FROM et_order ORDER BY et_order.id DESC) as et_order
INNER JOIN et_order_type ON et_order.type_id = et_order_type.id
...
This was very quick, but does not help at all, because i have to do the ordering not only on et_order, but also on fields of the joined tables.
Thanks in advance for your help!
Update:
Strange, when i change every inner join to a left one it works like a charme...
SELECT SQL_NO_CACHE * FROM et_order LEFT JOIN et_order_type ON et_order.type_id = et_order_type.id LEFT JOIN et_order_data ON et_order.id = et_order_data.order_id LEFT JOIN et_user et_user_consultant ON et_order.user_id_consulting = et_user_consultant.id LEFT JOIN et_customer ON et_order.customer_id = et_customer.id LEFT JOIN et_appointment ON et_order.appointment_id = et_appointment.id LEFT JOIN et_order_status order_status ON et_order.order_status_id = order_status.id LEFT JOIN et_status glass_r_status ON et_order_data.status_id_glass_r = glass_r_status.id LEFT JOIN et_status glass_l_status ON et_order_data.status_id_glass_l = glass_l_status.id
ORDER BY et_order.id DESC LIMIT 50
Does anyone know why?
