MySQL Forums
Forum List  »  Performance

query to find gaps in sequential indexes causes CPU spikes
Posted by: Chris Nevin
Date: November 22, 2011 12:50PM

I am relatively new to MySql having worked with Sql Server for years. I am trying to find the gaps within a table based upon two values, both ints for which there is an index available. The storage engine is innodb. When running the queries iteratively (maybe a dozen times), the cpu can spike to 50+%. An example of the queries I'm running are:

select column0 from CustomerData where column1 = 10 and column0 > 0 order by column0 limit 1

then using the column0 response from the first query (say 50) -

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

This gives the start and end column0 values of a block of rows, and column0 from the second query is used to start the next iteration.

I have an index of column1, column0. The combination is unique.

The second query takes about 3 seconds to complete with the subquery. I've used EXPLAIN and it is using my index to do a range look up and estimates a single row for the dependant subquery. The table is quite large, about 38 million rows and could have as much as million rows where column1 = 10.

Most of the posts I've seen regarding finding gaps are for Sql Server. Any ideas how I could reduce the cpu load on these queries would be greatly appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
query to find gaps in sequential indexes causes CPU spikes
2394
November 22, 2011 12:50PM


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.