CODEWITHSUNDEEP
mysqlsql-order-bylimit
user1406177
user1406177

Reputation: 1369

Huge dataset, ORDER BY date and limit

I have a MySQL table with ~3.5mio lines. I want to iterate over each line. Since i do not want to load all 3.5mio lines, i split up the query seperating it into small ones. For the split i am using the date field.

The problem now is: When i select from the table with lines bigger than a specified date, it still looks after every date, puts them in a temporary list and than cuts of 90% of the entries since i only want small parts.

How can i create my query so that my computer does not crash it loads the whole table?

Upvotes: 0

Views: 170

Answers (1)

spencer7593
spencer7593

Reputation: 108500

The short answer is to make sure your ORDER BY can be satisfied using an index, rather than requiring a filesort operation.

You can use EXPLAIN SELECT ... to get the query plan.

EXPLAIN
SELECT t.*
  FROM mytable t FORCE INDEX FOR ORDER BY (mytable_IX1)
 WHERE t.created_at > @last_created_at
    OR t.created_at = @last_created_at AND t.id > @last_id
 ORDER BY t.created_at, t.id
 LIMIT 1000


id select_type table  type  possible_keys key         key_len ref    rows Extra        
-- ----------- ------ ----- ------------- ----------- ------- ------ ---- -------------
 1 SIMPLE      t      range mytable_IX1   mytable_IX1 13      (NULL) 1000 Using where; Using index

On the first execution, you'd only need a t.created_at IS NOT NULL predicate. On subsequent executions, you would pass in the values from the columns of the last row you fetched, so you would start your next query from that point.

Upvotes: 1

Related Questions