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)
[/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.