I am working on a website in which the database is huge. 1 million records are in the table at the time. When I perform a query then it takes too much time to execute. One sample query is given below:
select * from `ratings` order by id limit 499500, 500
Every query takes more than one minute, but when I drop the table to 10 thousand records then this query executes fastly.
As I have read that there is not problem for 1 million records in a table because in database tables, there is not problem of big records.
I have used indexing of id in the table by the help of Stack Overflow question How do I add indices to MySQL tables?, but still I got the same problem.
*** I am using CodeIgniter for the project.