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.
I'm sorry I didn't wrote here. I use cols of these tables in SELECT clause. I cutted from this example it was too long. So I need them there.
And real user can sort result order by all of them cols.
The structure of tables editor, customer, product,.. look similiar like table orders.
The DISTINCT is needed because on one row in table orders can have access more user groups. So in the table acl_access where are more rows corresponded with a one row from table orders.
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.)
It sounds real. OK, here is the whole select:
EXPLAIN SELECT DISTINCT o.id AS id, o.orderid AS orderid, o.serialid AS serialid, CONCAT(o.orderid, '-', o.serialid, '-', IFNULL(e3.id, 0)) AS idss, e1.login AS createdBy, o.createdon AS createdOn, e2.login AS changedBy, o.changedon AS changedOn, o.cancel AS cancel, concat(c.lastname, ' ', c.firstname) AS customer, c.street AS street, c.city AS city, c.zip AS zip, c.email AS email, c.phone AS phone, c.cellphone AS cellphone, c.floor AS floor, c.lift AS lift, o.producttype AS producttype, o.warrantyid AS warrantyid, o.customerid AS customerid, o.scheduleid AS scheduleid, o.pnc AS pnc, o.seno AS seno, o.note AS note, o.descr AS descr, o.worktime AS worktime, o.workdescr AS workdescr, o.workprice AS workprice, o.materialprice AS materialprice, o.transprice AS transprice, o.papernumber AS papernumber, p.name AS product, m.name AS brand, cal.certaindate AS certaindate, e3.id AS engineerid, concat(e3.lastname, ' ', e3.firstname) AS engineer, pr.name AS company, o.express AS express, o.completed AS completed, o.authorized AS authorized
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
+----+-------------+-------+--------+-------------------+---------+---------+-----------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+---------+---------+-----------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | a | range | PRIMARY,gid,gid_2 | gid | 8 | NULL | 6371 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | o | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.a.objid | 1 | |
| 1 | SIMPLE | e1 | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.createdby | 1 | |
| 1 | SIMPLE | e2 | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.changedby | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.customerid | 1 | |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.productid | 1 | |
| 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.brandid | 1 | |
| 1 | SIMPLE | cal | eq_ref | id | id | 8 | servis_info3.o.scheduleid | 1 | |
| 1 | SIMPLE | e3 | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.cal.engineerid | 1 | |
| 1 | SIMPLE | pr | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.e3.partnerid | 1 | |
+----+-------------+-------+--------+-------------------+---------+---------+-----------------------------+------+-----------------------------------------------------------+
-- Thanks,
Pavel Novak
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
