MySQL Forums
Forum List  »  Optimizer & Parser

Re: ORDER BY and join: include columns that don't matter?
Posted by: Rick James
Date: May 12, 2010 08:04AM

Well, start by turning the subquery into a JOIN:
select  cardset, language, collectnum, collectnum_suffix, name
    from  cardinset s
    JOIN  cardname n   ON s.cardid = n.cardid
    JOIN  printrun p   ON s.cardset = p.cardset AND s.language = p.language
    order by  collectnum asc, collectnum_suffix asc;

Which will be faster? It may depend on the engine -- are you using MyISAM or InnoDB?

Please do EXPLAIN SELECT ... so we can see what the optimizer decided to do.

What about printrun's indexes? They are critical to the question. I would hope for either of:
INDEX(cardset, language, ...)
INDEX(language, cardset, ...)

The ORDER BY fields are in cardinset, right?

Could you simply do SHOW CREATE TABLE on all three tables.

If you are fetching all the data (except for the NOT EXISTS rows), it will be doing a full table scan on the first table (whichever EXPLAIN says is first), then reaching into each of the others.

How big are the tables? SHOW TABLE STATUS, please.

Options: ReplyQuote

Written By
Re: ORDER BY and join: include columns that don't matter?
May 12, 2010 08:04AM

Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.