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

ex:

I have: 1,2,3,5,7,8,9,10
This 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,
l.card_series AS start_card_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,
(SELECT
c.card_series
FROM
cards AS c
WHERE
end_control_series = c.control_series) AS end_card_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 l.product_id ASC
LIMIT 0, 10;


It will return the following result

[
'start_control_series' => "110",
'start_card_series' => '440',
'end_control_series' => '114',
'end_card_series' => '444'
]
.................................
.....................


The above "SELECT" used to execute on a 25.000 table rows, makes MySQL Workbenck stop working....even if I set "LIMIT 1"


Below you have a simplified version of the above SELECT which returns an answer if I put LIMIT 1 and remove 2 columns



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
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)


In the "WHERE r.control_series IS NULL" I will put some filters " AND product_id = 18 " and "HAVING 110 BETWEEN start_control_series AND end_control_series" .

- I'm interested in returning 10 results per page (LIMIT 1,10) in a fair amount of time and display them in a table (jQuery datatable).
- Will "HAVING" statement affect SELECT statement performance?


Thanks in advanced, hopefully someone can help me.