MySQL Forums
Forum List  »  Optimizer & Parser

ORDER BY and join: include columns that don't matter?
Posted by: Chris Hennick
Date: May 11, 2010 02:54AM

I have the following query that I expect to run frequently:

select cardset, language, collectnum, collectnum_suffix, name from cardinset, cardname where cardinset.cardid = cardname.cardid and exists (select 1 from printrun
where cardset = cardinset.cardset and language = cardname.language)
order by collectnum asc, collectnum_suffix asc;

cardname's indices are PRIMARY KEY (`cardid`,`language`), UNIQUE KEY `language` (`language`,`name`)

cardinset's indices are PRIMARY KEY (`cardset`,`collectnum`,`collectnum_suffix`), KEY `cardid` (`cardid`,`cardset`,`collectnum`,`collectnum_suffix`)

Will this query run faster if I also order by cardset and/or language? (The ordering by collectnum and collectnum_suffix only needs to hold within each (cardset,language) pair, since the PHP layer puts the output into a 2D associative array.) Can I speed it up by adding or changing indices?

Options: ReplyQuote

Written By
ORDER BY and join: include columns that don't matter?
May 11, 2010 02:54AM

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.