Re: 25.000+ rows - MySQL SELECT consecutive numbers performance issue

[Daniel Negoita]

I have tried to be as explicit as I could. Below you have the query explained in details

What I want to obtain? - I want to SELECT start_number and end_number from a set of consecutive numbers from a table

ex:

I have a column with values: 1,2,3,5,7,8,9,10
If I run the SELECT on column will return: (1,3), (5), (7), (8,10)

So my result will look something like :

[
'start_number' => 1,
'end_number' => 3
]
.............
.........
The problem is that the table contains a large number of rows (25.000+...for now) and it takes ages to execute (I have limited the number of results returned but still didn't fixed the execution time)

SELECT
l.control_series AS start_control_series,
(SELECT
MIN(a.control_series) AS id
FROM
cards AS a
LEFT OUTER JOIN
cards AS b ON a.control_series = b.control_series - 1
WHERE
b.control_series IS NULL
AND a.control_series >= l.control_series) AS end_control_series
FROM
cards AS l
LEFT OUTER JOIN
cards AS r ON r.control_series = l.control_series - 1
WHERE
r.control_series IS NULL
ORDER BY product_id ASC
LIMIT 1;


Answer returned:

[
'start_control_series' => "110",
'end_control_series' => '114',
]

MySQLWorkbench response: 1 row(s) returned 304.829 sec / 0.000 sec (as you can see it takes ages)
Table (cards) has 3 columns:

row_id (int primary autoincrement)
control_series (bigint)
product_id (int)
Running MySQL 5.1, on a shared host

I'm want to return 10 results (LIMIT 1,10) in a fair amount of time

Thanks in advanced, hopefully someone can help me.