very slow select with join

Pavel Novak Fri, 28 Jan 2005 07:33:26 -0800

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)


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)


And when I try this select (without JOIN order_acl only) it's quick too:

(...table...)
20 rows in set (1.11 sec)


Do you know what's wrong? :(

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)


--
Best Regards

Pavel Novak


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

very slow select with join

Reply via email to