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:
[LIST]
[]row_id (int primary autoincrement)
[]control_series (bigint)
[*]product_id (int)
[/LIST] 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.
