Pavel Novak wrote:
Hello, I am clueless of this query, becaouse it's very slow - between 30 and 60 seconds:
mysql> SELECT DISTINCT o.id AS id, o.orderid AS orderid, o.serialid AS serialid
-> FROM orders o
-> LEFT JOIN editor e1 ON o.createdby = e1.id
-> LEFT JOIN editor e2 ON o.changedby = e2.id
-> LEFT JOIN customer c ON o.customerid = c.id
-> LEFT JOIN product p ON o.productid = p.id
-> LEFT JOIN brand m ON o.brandid = m.id
-> LEFT JOIN calendar cal ON cal.id = o.scheduleId
-> LEFT JOIN editor e3 ON cal.engineerid = e3.id
-> LEFT JOIN partner pr ON e3.partnerid = pr.id
-> JOIN order_acl a ON a.objid = o.id && a.gid IN (3, 4, 302, 303,
-> 312)
-> ORDER BY o.id DESC
-> LIMIT 20;
(...table...) 20 rows in set (28.66 sec)
Is this really your query? The LEFT JOINed tables, editor, customer, product, brand, calendar, and partner do not contribute to the output (i.e. none of their columns are selected), nor are they used to determine which rows to consider (i.e. they don't appear in the WHERE clause), so I don't see why they are included in the query. Also, unless the JOINs are creating extra rows of output, I don't see a need for DISTINCT. It appears to me that
SELECT o.id, o.orderid, o.serialid FROM orders o JOIN order_acl a ON a.objid = o.id WHERE a.gid IN (3, 4, 302, 303, 312) ORDER BY o.id DESC LIMIT 20;
is equivalent.
When I try get data from table order_acl only it's quick:
mysql> SELECT DISTINCT objid -> FROM order_acl -> WHERE gid IN (1, 2, 3, 4, 213, 214, 5021) -> LIMIT 20;
(...table...) 20 rows in set (0.30 sec)
This is a simple SELECT using the index on gid to choose rows.
And when I try this select (without JOIN order_acl only) it's quick too:
mysql> SELECT DISTINCT o.id AS id, o.orderid AS orderid, o.serialid AS serialid
-> FROM orders o
-> LEFT JOIN editor e1 ON o.createdby = e1.id
-> LEFT JOIN editor e2 ON o.changedby = e2.id
-> LEFT JOIN customer c ON o.customerid = c.id
-> LEFT JOIN product p ON o.productid = p.id
-> LEFT JOIN brand m ON o.brandid = m.id
-> LEFT JOIN calendar cal ON cal.id = o.scheduleId
-> LEFT JOIN editor e3 ON cal.engineerid = e3.id
-> LEFT JOIN partner pr ON e3.partnerid = pr.id
-> ORDER BY o.id DESC
-> LIMIT 20;
(...table...) 20 rows in set (1.11 sec)
This simply selects all rows in o and joins them to the other tables, presumably using an appropriate index for each. The index on o.id can be used to retrieve the rows in the proper order (ORDER BY) in the first place, which means we don't have to sort, and we can simply quit after the first 20 rows are found (LIMIT).
Do you know what's wrong? :(
Your query filters rows based on the gid value in order_acl, but sorts based on the value in orders.id. I expect mysql has two choices: grab all rows in orders presorted by id using the index, join them to the appropriate rows in order_acl using the index on a.objid, then throw out the rows with the wrong a.gid, OR grab the rows in order_acl with the correct a.gid by using the index, then join them to the appropriate rows in orders using the index on o.id to match them up, and then sort the results on o.id. My guess is that mysql chooses the latter, as it visits far fewer rows, but then it must save the results in a temp table and do a filesort at the end. Of course, I'm speculating. What does EXPLAIN say for your query? (The EXPLAIN below is for the wrong query.)
mysql> EXPLAIN SELECT DISTINCT o.id AS id, o.orderid AS orderid, o.serialid AS serialid
-> FROM orders o
-> LEFT JOIN editor e1 ON o.createdby = e1.id
-> LEFT JOIN editor e2 ON o.changedby = e2.id
-> LEFT JOIN customer c ON o.customerid = c.id
-> LEFT JOIN product p ON o.productid = p.id
-> LEFT JOIN brand m ON o.brandid = m.id
-> LEFT JOIN calendar cal ON cal.id = o.scheduleId
-> LEFT JOIN editor e3 ON cal.engineerid = e3.id
-> LEFT JOIN partner pr ON e3.partnerid = pr.id
-> ORDER BY o.id DESC
-> LIMIT 20;
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 20402 | Using temporary; Using filesort |
| 1 | SIMPLE | e1 | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.createdby | 1 | Using index; Distinct |
| 1 | SIMPLE | e2 | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.changedby | 1 | Using index; Distinct |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.customerid | 1 | Using index; Distinct |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.productid | 1 | Using index; Distinct |
| 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.brandid | 1 | Using index; Distinct |
| 1 | SIMPLE | cal | eq_ref | id | id | 8 | servis_info3.o.scheduleid | 1 | Distinct |
| 1 | SIMPLE | e3 | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.cal.engineerid | 1 | Distinct |
| 1 | SIMPLE | pr | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.e3.partnerid | 1 | Using index; Distinct |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
9 rows in set (0.30 sec)
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
