MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing query
Posted by: Rick James
Date: February 25, 2012 05:54PM

IN (SELECT...) is poorly optimized; use JOIN instead.
select  status,name,surname,address
    from  database
    where  status in ('owner','renting')
      and  userid not in (
        SELECT  userid
            from  database
            where  status in ('evicted','moved'))
      and  city = 'Detroit';
-->
select  a.status, a.name, a.surname, a.address
    from       database a
    left join  database b on a.userid = b.userid   -- "self join"
    where  a.status in ('owner','renting')
      and  b.status in ('evicted','moved'))
      and  a.city = 'Detroit'
      and  b.userid is null;    -- same effect as NOT IN

You are looking for Detroit owners/renters who have not been kicked out of some other city? And a userid can occur more than once in the table?

Please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?

Assuming that ownerid is not UNIQUE, I would suggest adding two indexes:
INDEX(ownerid, status),
INDEX(ownerid, city, status)

Options: ReplyQuote


Subject
Views
Written By
Posted
1713
February 24, 2012 06:07AM
963
February 24, 2012 01:16PM
851
February 25, 2012 03:49AM
1134
February 27, 2012 05:57AM
Re: Optimizing query
903
February 25, 2012 05:54PM
833
February 27, 2012 06:04AM
910
February 28, 2012 07:57PM


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.