Asked  7 Months ago    Answers:  5   Viewed   56 times

Scenario in short: A table with more than 16 million records [2GB in size]. The higher LIMIT offset with SELECT, the slower the query becomes, when using ORDER BY *primary_key*

So

SELECT * FROM large ORDER BY `id`  LIMIT 0, 30 

takes far less than

SELECT * FROM large ORDER BY `id` LIMIT 10000, 30 

That only orders 30 records and same eitherway. So it's not the overhead from ORDER BY.
Now when fetching the latest 30 rows it takes around 180 seconds. How can I optimize that simple query?

 Answers

73

It's normal that higher offsets slow the query down, since the query needs to count off the first OFFSET + LIMIT records (and take only LIMIT of them). The higher is this value, the longer the query runs.

The query cannot go right to OFFSET because, first, the records can be of different length, and, second, there can be gaps from deleted records. It needs to check and count each record on its way.

Assuming that id is the primary key of a MyISAM table, or a unique non-primary key field on an InnoDB table, you can speed it up by using this trick:

SELECT  t.* 
FROM    (
        SELECT  id
        FROM    mytable
        ORDER BY
                id
        LIMIT 10000, 30
        ) q
JOIN    mytable t
ON      t.id = q.id

See this article:

  • MySQL ORDER BY / LIMIT performance: late row lookups
Tuesday, June 1, 2021
 
liquidmotion
answered 7 Months ago
33

if the result set is empty mysql_data_seek() will fail with a E_WARNING. That is I think happening in you case because you are not checking whether the result set is empty or not before calling the mysql_data_seek().

Always check the result for number of rows if they are >=1 then you are safe to call mysql_data_seek()

Saturday, May 29, 2021
 
Santi
answered 7 Months ago
45

You cannot specify offset in DELETE's LIMIT clause.

So the only way to do that is to rewrite your query to something like:

DELETE FROM `chat_messages` WHERE id IN (select id from (select id
                                           FROM `chat_messages`
                                       ORDER BY `timestamp` DESC
                                          LIMIT 20, 50) x)

Supposing that you have primary key id column

UPD: you need to implement double nesting to fool mysql, since it doesn't allow to select from currently modified table (thanks to Martin Smith)

Saturday, June 26, 2021
 
Sidarta
answered 6 Months ago
65

normally unix timestamp range is from January 1st 1970 to December 31st 2037 for more information have a look at http://en.wikipedia.org/wiki/Year_2038_problem

Monday, August 23, 2021
 
SilverHorn
answered 4 Months ago
56

Use a CASE statement in the ORDER BY:

ORDER BY CASE someColumn
           WHEN value1 THEN 1
           WHEN value2 THEN 2
           WHEN value3 THEN 3
         END ASC

Assign the arbitrary values as you like. I don't normally include ASC in ORDER BY because it is implied if not defined, but I wanted to be explicit in case you want in DESC order.

Saturday, November 13, 2021
 
BartmanEH
answered 3 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share