MySQL Forums
Forum List  »  Performance

Re: 25.000+ rows - MySQL SELECT consecutive numbers performance issue
Posted by: Peter Brawley
Date: December 12, 2014 12:18PM

Finding sequence starts & ends (see that topic at http://www.artfulsoftware.com/queries.php) will never be really quick because it needs left joins, which don't optimise well.

But I'm pretty sure you'll improve your query's performance by moving the subqueries to the FROM clause. Given table ints(i primary key), the fastest query I know to find sequence starts & ends is ...

select  a.i as start, min( c.i ) as end  
from ints as a 
left join ints as b on a.i = b.i + 1 
left join ints as c on a.i <= c.i 
left join ints as d on c.i+1 = d.i 
where b.i is null  
  and c.i is not null 
  and d.i is null 
group by a.i;

On a table of 1k sequential ints with 40 values missing, that returns the 40 start-end pairs in a third of a second on a very modest laptop.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: 25.000+ rows - MySQL SELECT consecutive numbers performance issue
1074
December 12, 2014 12:18PM


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.