2

I have 8 millions of records in table and this query is just too slow. It's for sitemap (that we need to index it all). In this example I choose 1000 items from position 6 millions.

SELECT source, identifier
FROM mh_download
WHERE deleted =0
LIMIT 6000000 , 1000

33 seconds

Table is MyISAM and column deleted has index. MySQL version 5.5.41.

Filburt
  • 17,626
  • 12
  • 64
  • 115
Manic Depression
  • 1,000
  • 2
  • 16
  • 34
  • 4
    Without order by your LIMIT will return indeterminate results.So why start at 600.000 then?Try adding ORDER BY either source or identifier before LIMIT,depending on which one has an index – Mihai Jul 06 '15 at 06:39
  • I need go throw all records in table. If you know better option, please tell me. (also thanks for edit) – Manic Depression Jul 06 '15 at 06:41
  • 1
    You can find some interesting reading material on the MySQL site on this topic: https://dev.mysql.com/doc/refman/5.5/en/limit-optimization.html – ndsmyter Jul 06 '15 at 06:42

1 Answers1

6

I found duplicate Why does MYSQL higher LIMIT offset slow the query down?

so best solution will be

1)Hold the last id of a set of data(30) (e.g. lastId = 530)
2)Add the condition "WHERE id > lastId limit 0,30"

Thank you all for your patience

Community
  • 1
  • 1
Manic Depression
  • 1,000
  • 2
  • 16
  • 34