MySQL Forums
Forum List  »  Optimizer & Parser

Order By Slows down Joins
Posted by: Sami BH
Date: August 22, 2012 09:12AM

Hello,

I have problem with this query

SELECT *
FROM files f
LEFT JOIN xfiles x ON x.hash = f.bhash
LEFT JOIN cate c ON c.id = f.cate
LEFT JOIN users u ON u.id = f.user
LEFT JOIN userslev ul ON u.id_lev = ul.id
LEFT JOIN (

SELECT DISTINCT x.hash, s.iis
FROM anno x
JOIN seb s ON s.iis = x.icc
WHERE x.le =0
AND (
x.eve =0
OR x.eve =2
)
GROUP BY `x`.`hash`
) AS tabel2 ON tabel2.hash = f.bhash
WHERE f.see + IFNULL( x.see, 0 ) + f.lee + IFNULL( x.lee, 0 ) >0
ORDER BY f.added DESC

Showing rows 0 - 29 ( 22,083 total, Query took 9.6523 sec)

as you can see , it takes 9 sec !

but without 'order by'

SELECT *
FROM files f
LEFT JOIN xfiles x ON x.hash = f.bhash
LEFT JOIN cate c ON c.id = f.cate
LEFT JOIN users u ON u.id = f.user
LEFT JOIN userslev ul ON u.id_lev = ul.id
LEFT JOIN (

SELECT DISTINCT x.hash, s.iis
FROM anno x
JOIN seb s ON s.iis = x.icc
WHERE x.le =0
AND (
x.eve =0
OR x.eve =2
)
GROUP BY `x`.`hash`
) AS tabel2 ON tabel2.hash = f.bhash
WHERE f.see + IFNULL( x.see, 0 ) + f.lee + IFNULL( x.lee, 0 ) >0


Showing rows 0 - 29 ( 22,083 total, Query took 0.0241 sec)

only 0.0241 sec :)

............

is there a way to have ' order by' with good-time?

............

indexes:


Indexes: (files)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No ihash 27815 A
Edit Drop filename BTREE No No filename 27815 A
Edit Drop cate BTREE No No cate 45 A
Edit Drop user BTREE No No user 1264 A
Edit Drop bhash BTREE No No bhash (20) 27815 A
Edit Drop added BTREE No No added 27815 A
Edit Drop filename_2 FULLTEXT No No filename 1



Indexes: (xfiles)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No id 8196 A
Edit Drop hash BTREE Yes No hash 8196 A
Edit Drop see BTREE No No see 37 A
Edit Drop leec BTREE No No lee25 A


Indexes: (cate)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No id 51 A


Indexes: (users)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No id 39776 A
Edit Drop username BTREE Yes No username 39776 A
Edit Drop id_lev BTREE No No id_lev 19 A


Indexes: (userslev)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop base BTREE Yes No id 22 A


Indexes: (anno)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No id 23636 A
Edit Drop icc BTREE No No icc 2626 A
Edit Drop eve BTREE No No eve 8 A
Edit Drop hash BTREE No No hash 5909 A
Edit Drop le BTREE No No le 875 A


Indexes: (seb)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No id 58 A
Edit Drop iis BTREE Yes No iis 58 A YES


EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY f ALL NULL NULL NULL NULL 27819 Using temporary; Using filesort
1 PRIMARY x eq_ref hash hash 20 f.bhash 1 Using where
1 PRIMARY c eq_ref PRIMARY PRIMARY 4 f.cate 1
1 PRIMARY u eq_ref PRIMARY PRIMARY 4 f.user 1
1 PRIMARY ul eq_ref base base 4 u.id_lev 1
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1176
2 DERIVED s index iis iis 5 NULL 58 Using index; Using temporary; Using filesort
2 DERIVED x ref icc,eve,le icc 4 s.iis 9 Using where

Options: ReplyQuote


Subject
Views
Written By
Posted
Order By Slows down Joins
3766
August 22, 2012 09:12AM
1217
August 23, 2012 08:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.