Re: query to find gaps in sequential indexes causes CPU spikes

[Chris Nevin]

Explain shows two rows:

select_type type key key_len ref rows extra
primary range column1_column0 8 NULL 1250268 Using where; Using index
dependant subquery ref column1_column0 8 const,func 1 Using where; Using index

The server is an AWS x-large RDS instance spec'd as having 4 virtual cores and 15 GB of RAM.

I'm actually trying to find the start and ends of a block - so given your numbers my output would look like:

1 2
6 7
9 12
15 15

I realize there are other ways to write the queries to cut down on the number of iterations. But so far through my experimentation, I haven't found anything yet that's easier on the cpu. Any ideas on how to optimize (especially for mysql since I've seen some major differences in the performance between mysql and sql server) would be greatly appreciated.