Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

25.000+ rows - SELECT consecutive numbers performance issue

daniel.negoitadaniel.negoita EntrantInactive User Role Beginner
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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.