MySQL Forums
Forum List  »  Optimizer & Parser

Re: basic 2 table query slow - where, index?
Posted by: Rick James
Date: December 05, 2010 12:56PM

select  table1.id, table1.DateOpened, table1.Status,
        table2.Name, etc
    from  table1
    join  table2   on table1.CurrentName = table2.id
    where  table1.Type = 'Add'
      and  (Status = 'Open'
              OR  Status = 'Pending');

Order does not matter in JOIN (unless you say STRAIGHT_JOIN).
Order does not matter in WHERE.

and (Status = 'Open'
OR Status = 'Pending')
-->
Status IN ('Open', 'Pending')

Do you have these indexes?

If you do EXPLAIN EXTENDED on the SELECT, and then do SHOW WARNINGS, you may find that the ON clause turned into a WHERE clause anyway. Don't worry about it. OTOH, there can be different semantics when the condition is in the ON versus the WHERE.

(3b) If you had
INDEX(Type, Status)
it _might_ use that index and run faster. Or it might not. That is, if "too many" of the rows have the indicated Type and Status, it would decide that is is more efficient to ignore the index and simply do a "table scan" rather than bouncing between the index and the data.

Another thing to try (after adding the INDEX I suggested):
select  table1.id, table1.DateOpened, table1.Status,
        table2.Name, etc
    from  table1
    join  table2   on table1.CurrentName = table2.id
    where  table1.Type = 'Add'
      and  Status = 'Open'
UNION ALL
select  table1.id, table1.DateOpened, table1.Status,
        table2.Name, etc
    from  table1
    join  table2   on table1.CurrentName = table2.id
    where  table1.Type = 'Add'
      and  Status = 'Pending'
;

Options: ReplyQuote


Subject
Views
Written By
Posted
2596
December 01, 2010 02:55PM
Re: basic 2 table query slow - where, index?
1272
December 05, 2010 12:56PM


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.