MySQL Forums
Forum List  »  Performance

Re: query to find gaps in sequential indexes causes CPU spikes
Posted by: Rick James
Date: November 26, 2011 06:02PM

You have:
select  column0
    from  CustomerData as cd
    where  column1 = 10
      and  column0 >= 50
      AND  
      ( SELECT  count(*)
            from  CustomerData
            where  column1 = 10
              and  column0 = cd.column0 + 1) = 0
    order by  column0
    limit  1
Competing products sometimes to a better job of optimizing queries.

This is virtually the same, and might be better optimized by MySQL:
select  column0
    from  CustomerData as cd
    where  column1 = 10
      and  column0 >= 50
      AND  
      NOT EXISTS ( SELECT  *
            from  CustomerData
            where  column1 = 10
              and  column0 = cd.column0 + 1)
    order by  column0
    limit  1

To further assist in analyzing slow SELECTs, 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?

Do you have a compound index on (column1, column0)

In particular, have you tuned the caches? --
http://mysql.rjweb.org/doc.php/memory

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: query to find gaps in sequential indexes causes CPU spikes
817
November 26, 2011 06:02PM


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.