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.

Do you use [indexes](https://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html) in your table? Also, 1M records is not that big for MySQL. — D4V1D, Nov 12 '15 at 07:17
table has three columns ranking_id, id and website.... i have not concept of indexing... but there should not be a problem for 1M records — M Razwan, Nov 12 '15 at 07:19
indexes in place? You are bringing back 500 rows, not 1M (or half that) — Drew, Nov 12 '15 at 07:19
There should be a problem if no indexes are used. You definitely need an index on `id` — Giorgos Betsos, Nov 12 '15 at 07:20
from the manual `SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15` — Drew, Nov 12 '15 at 07:21
Can you give a snapshot of the execution plan of the query. That should be helpful for all of us. — Abhay Chauhan, Nov 12 '15 at 07:26
SELECT * is a problem, ORDER BY LIMIT X ,y can only be accomplished by a scan,it has to get all the rows before ordering them — Mihai, Nov 12 '15 at 07:46
If this is for pagination don't let people page in to arbitrary page numbers like 1,000. — Martin Smith, Nov 12 '15 at 13:33
That query requires stepping over 499500 row before getting to the 500 you want. That takes time, regardless of the indexing. Why the heck do you want to do that? — Rick James, Sep 05 '16 at 19:22
@Mihai - if there is an index exactly matching the `ORDER BY`, it may use the index and not have to "get **all** the rows", but only 500000 in this case. — Rick James, Sep 05 '16 at 19:23

score 15 · Accepted Answer · edited Dec 08 '15 at 00:06

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.

">=" only works if there are no gaps in the ids! (And, yes, InnoDB and MyISAM should work similarly for _this_ case.) — Rick James, Sep 05 '16 at 19:19
@RickJames the engine was chosen because innodb would have caused range allocation gaps during the self-inserts — Drew, Sep 05 '16 at 19:21
One can always mess with the innodb mode like [here](http://stackoverflow.com/a/38363271) but I basically wasn't up for it at the moment. — Drew, Sep 05 '16 at 19:27

score 1 · Answer 2 · edited May 23 '17 at 12:24

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:

Fragmentation of Data pages
Statistics of table not updated
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.

score 1 · Answer 3 · edited Nov 12 '15 at 12:35

Checklist:

Created clustered index (index) on 'id' column order by ASC
Check your query time from command line, see this
Check your PHP code (I see you using CodeIgniter). About your algorithm: Did you use the CodeIgniter database library see this?
If you are using a raw query, check your loop.

Alternative way is to try a query using to an active index:

select * from `ratings` where id>=456789 limit 500;

score 0 · Answer 4 · edited Nov 12 '15 at 12:29

0

Check for indexing. There should be a primary key in each table and there may attributes in your where clause of your query that are not part of the primary index or of a clustered index.

edited Nov 12 '15 at 12:29

Peter Mortensen

30,738
21
105
131

answered Nov 12 '15 at 07:24

user2967688

1

1

I am not using where condition in the query. – M Razwan Nov 12 '15 at 07:26

score 0 · Answer 5 · edited May 23 '17 at 11:53

0

If you are using the InnoDB engine for your table, change it to MyISAM. It is faster for read operations. When to use MyISAM and InnoDB.

In addition, if you have queries with search criteria, make sure frequently used column are indexed.

edited May 23 '17 at 11:53

Community

1
1

answered Nov 12 '15 at 07:33

Genoud Magloire

555
6
15

score -1 · Answer 6 · answered Nov 12 '15 at 07:24

-1

Here is the link of Tuts-plus which will help you to solve your problem .

Most Probable solution is indexing the database and instead of (*) use column names Better way to Query

answered Nov 12 '15 at 07:24

sapphire code

71
1
3

MySQL statement takes more than minute to execute

6 Answers6

Linked