CODEWITHSUNDEEP
mysqlmyisam
Manic Depression
Manic Depression

Reputation: 1030

speed up LIMIT query with millions of records

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.

Upvotes: 2

Views: 1039

Answers (1)

Manic Depression
Manic Depression

Reputation: 1030

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

Upvotes: 6

Related Questions