• 6
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

MySQL statement takes more than minute to execute

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.

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.

  • 15
Reply Report

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.

  • 1
Reply Report

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;
  • 1
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions