MySQL operation takes more than a minute to complete

Question

I am working on a site where the database is huge. There were 1 million records in the table at that time. When I execute the request, it takes too much time to complete. The following is one example:

select * from `ratings` order by id limit 499500, 500 

Each query takes more than one minute, but when I drop the table to 10 thousand records, this query is fast.

As I said, there is no problem for 1 million records in a table, because there are no problems with large records in database tables.

I used the id indexing on the table using the stack overflow question. How do I add indexes to MySQL tables? but still i have the same problem.

*** I am using CodeIgniter for a project.

Answer 1

Please note: this does not mean to use MyISAM for a minute. I use this only so that my identifiers, min, max and count are in the queue. Therefore, ignore the engine.

 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 lines 4.7M

 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 | +----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+ 

The moral of the story may be to use a where clause.

The possibility of a dead end cannot be ruled out.

Answer 2

Start by checking the query execution plan to determine the bottleneck and create indexes if necessary. I think you should have an index in the Id column.

There are many factors that can also affect the performance of your request:

• Fragmentation of data pages
• Table statistics not updated
• Many queries work in parallel

and much more....

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

How to optimize MySQL queries based on EXPLAIN plan

Let me know if you encounter any problems.

Answer 3

Checklist:

• A clustered index (index) is created in the "id" column by ASC
• Check the query time from the command line, see this
• Check out the PHP code (I see that you are using CodeIgniter ). About your algorithm: you used the CodeIgniter database library see this ?
  If you are using a raw request, check your loop.

An alternative way is to attempt a query using the active index:

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

Answer 4

Check indexing. Each table must have a primary key and in your where clause of your query there may be attributes that are not part of the main index or the clustered index.

Answer 5

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

The most likely solution is to index the database, and instead of (*) using column names is the best way to query

Answer 6

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

Also, if you have queries with search criteria, make sure that a frequently used column is indexed.