Skip navigation links

MySQL Forums :: Performance :: query to find gaps in sequential indexes causes CPU spikes


Advanced Search

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
query to find gaps in sequential indexes causes CPU spikes 1407 Chris Nevin 11/22/2011 12:50PM
Re: query to find gaps in sequential indexes causes CPU spikes 637 Rick James 11/23/2011 11:01PM
Re: query to find gaps in sequential indexes causes CPU spikes 703 Chris Nevin 11/25/2011 05:19PM
Re: query to find gaps in sequential indexes causes CPU spikes 640 Rick James 11/26/2011 06:02PM
Re: query to find gaps in sequential indexes causes CPU spikes 820 Chris Nevin 11/28/2011 04:58PM
Re: query to find gaps in sequential indexes causes CPU spikes 729 Rick James 11/29/2011 09:23PM
Re: query to find gaps in sequential indexes causes CPU spikes 477 Chris Nevin 11/30/2011 10:41AM
Re: query to find gaps in sequential indexes causes CPU spikes 736 Rick James 12/01/2011 10:13AM


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.