I have a rather complicated query with many joins that works very well, without ordering. But as soon as I try to order from any of my fields, it runs very slowly and takes about 30 seconds.
Here's the request:
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 larger and has various WHERE operations, but even the basic query without any conditions is unreasonably slow. When I delete ORDER BY et_order.id DESC, the query takes about 0.01 seconds to retrieve.
In my original query, I select each individual field that I need separately - I just changed it to "SELECT *" now for better readability of the instruction.
The explanation of 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 really don't understand is due to why the select explanation says that it does not use any keys for et_order_type. Maybe because it is not necessary, since it has only 4 lines?
But et_order has an index for type_id: KEY type_id ( type_id )
I added (one) INDEX for each key that I use to attach and order. Could this be a 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 content is negligible.
When I just join et_order_data and et_order_type, it also takes a lot of time, and the select explanation still says the NULL key 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 viewed here: http://pastebin.com/PED6Edyx
Any tips on optimizing my query?
I tried ordering in a subquery, for example:
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 ...
It was very fast, but it does not help at all, because I have to place an order not only for et_order, but also in the fields of the joined tables.
Thanks in advance for your help!
Update:
Strange, when I change every inner join to a left, it works like a spell ...
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?