MySQL statement takes more than minute to execute

Question

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.

Answer 1

Note, this is not suggesting for a minute to use MyISAM. I use that only to get my ids, min,max, and count to line up. So ignore the engine, please.

create table ratings
(   id int auto_increment primary key,
    thing int null
)engine=MyISAM;
insert ratings (thing) values (null),(null),(null),(null),(null),(null),(null),(null),(null);
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;

insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;

insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;

I now have 4.7M rows

select count(*),min(id),max(id) from ratings;
+----------+---------+---------+
| count(*) | min(id) | max(id) |
+----------+---------+---------+
|  4718592 |       1 | 4718592 |
+----------+---------+---------+
select * from `ratings` order by id limit 499500, 500;
-- 1 second on a dumpy laptop

.

explain select * from `ratings` order by id limit 499500, 500;
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | ratings | ALL  | NULL          | NULL | NULL    | NULL | 4718592 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+

.

explain select * from `ratings` where id>=499501 limit 500;
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra                 |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+
|  1 | SIMPLE      | ratings | range | PRIMARY       | PRIMARY | 4       | NULL | 4198581 | Using index condition |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+

Moral of the story may be to use a where clause.

One cannot rule out the possibility of a deadlock.

Answer 2

Start with checking the execution plan of the query to identify the bottleneck and create indexes if required. I think you should atleast have a index on Id column.

There are many factors which can also affect your query performance:

1. Fragmentation of Data pages
2. Statistics of table not updated
3. Many request running in parallel

and many more....

Follow the below links to get the execution plan and to identify the performance degrading factors: http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

How to optimise MySQL queries based on EXPLAIN plan

Let me know if you face any troubles.