MySQL - Traag door de order by

[Verwijderd]

Ik heb de volgende query gemaakt:

code:

SELECT m.nmatchid
FROM  tblmatching m  
INNER JOIN tblarticlecategory acl ON m.narticlecategoryleft = acl.narticlecategoryid  
INNER JOIN tblarticlecategory acr ON m.narticlecategoryright = acr.narticlecategoryid  
LEFT JOIN tblmatchresult mr ON m.nmatchid = mr.nmatchid AND mr.cip = '127.0.0.1' 
WHERE m.bactive = 1
AND mr.nmatchresultid IS NULL  
AND acl.bactive = 1
AND acl.ncategoryid = 5 
AND acr.bactive = 1
LIMIT 1

Deze query is verder super snel (Query took 0.0057 sec)

Nu komt er echt een ORDER BY m.norder bij, om de tabel op te sorteren en vervolgens gaan we richting de 10 seconden of soms nog langer.

De tabel matching bestaat op dit moment uit zon 20 miljoen records.

Een explain van de query, met ORDER BY, geeft dit weer:

code:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  acl ref PRIMARY,IX_tblarticlecategory_unique_idx,narticlei...   ncategoryid_idx 4   const   2016    Using where; Using temporary; Using filesort
1   SIMPLE  al  eq_ref  PRIMARY,IX_tblarticle_price_idx PRIMARY 4   ac0299a.acl.narticleid  1   Using where
1   SIMPLE  m   ref IX_tblmatching_match_idx,narticlecategoryleft_idx,...   IX_tblmatching_match_idx    4   ac0299a.acl.narticlecategoryid  315 Using where
1   SIMPLE  acr eq_ref  PRIMARY,IX_tblarticlecategory_unique_idx,narticlei...   PRIMARY 4   ac0299a.m.narticlecategoryright 1   Using where
1   SIMPLE  ar  eq_ref  PRIMARY,IX_tblarticle_price_idx PRIMARY 4   ac0299a.acr.narticleid  1   Using where
1   SIMPLE  mr  ref nmatchid_idx    nmatchid_idx    8   ac0299a.m.nmatchid  1   Using where; Not exists

Heb dus verschillende indexes gemaakt en die lijkt hij wel te pakken maar nog blijft hij traag.

Moet toch sneller kunnen denk ik zo ...

[P_de_B]

'Using filesort'

Dat betekent dat hij niet een index gebruikt voor de ORDER BY.

Zie http://dev.mysql.com/doc/...rder-by-optimization.html voor meer informatie

[borft]

Een van de redenen waarom de order by traag is, is dat mysql de volledige resultset moet uitrekenen, voordat zelfs maar met de order by begonnen kan worden. De truc is om bij je explain te kjiken welke criteria het grootste aantal permutaties hebben. Deze wil je omlaag brengen, en je wilt zeker weten dat een index gebruikt wordt. Dus geen filesort en geen temptables

[Verwijderd]

thx, dat had ik over het hoofd gezien.

ik heb de volgende indexes op die table, maar nog pakt hij hem niet

code:

Keyname                      Type   Unique  Packed   Field                   Cardinality
PRIMARY                      BTREE  Yes     No       nmatchid                14170554       
IX_tblmatching_match_idx     BTREE  Yes     No       narticlecategoryleft    22141      
                                                     narticlecategoryright   14170554   
narticlecategoryleft_idx     BTREE  No      No       narticlecategoryleft    16690      
narticlecategoryright_idx    BTREE  No      No       narticlecategoryright   59540      
IX_tblmatching_norder_idx    BTREE  No      No       norder                  14170554       
bactive                      BTREE  No      No       bactive                 17     
IX_tblmatching_bactivenorder BTREE  No      No       bactive                 17     
                                                     norder                  14170554

[Voutloos]

Welke indexes zijn er? Query moet met een composite index niet eens zo heel moeilijk zijn.

[borft]

als je geen limit doet (en bv een count), hoeveel results heeft je query dan?

[GlowMouse]

Om je composite index op (bactive,norder) te kunnen gebruiken, moet je tabel m de eerste tabel zijn in de explain output. MySQL kiest ervoor om de joinvolgorde om te draaien. Wanneer je alles op tabel m zou joinen en acl.bactive = XX AND acl.ncategoryid = XX uit je query weg zou laten en dan altijd snel rijen zou vinden die hieraan voldoen, dan kun je met STRAIGHT_JOIN een bepaalde joinvolgorde forceren.

[Verwijderd]

het ziet er nu beter uit, hij gaat nu wel snel, maar heb inderdaad een aantal zaken uitgezet, ga nu langzaam de query weer uitbreiden en kijken of alles snel gaat blijven.

Bedankt!