MySQL statement takes more than minute to execute

by

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.

More Related Contents:

  1. Trying to Show 87 mysql rows in 3 html tables using PHP and mysql [closed]
  2. While loop inserts three times
  3. Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in? [duplicate]
  4. regarding installation of php in windows
  5. php nested while loop. Inner loop works only once [closed]
  6. Problame with files on SQL
  7. save user images in MYSQL database by which method? [closed]
  8. Row count with PDO
  9. Best way to INSERT many values in mysqli?
  10. How to view query error in PDO PHP
  11. php/mysql with multiple queries
  12. How to populate second dropdown based on selection of first dropdown using jQuery/AJAX and PHP/MySQL?
  13. Resetting array pointer in PDO results
  14. Notice: Array to string conversion in
  15. How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?
  16. Find total number of results in mySQL query with offset+limit
  17. How to efficiently find the closest locations nearby a given location
  18. PDO::PARAM for type decimal?
  19. How to set up entity (doctrine) for database view in Symfony 2
  20. (mysql, php) How to get auto_increment field value before inserting data?
  21. How to execute raw queries with Laravel 5.1?
  22. Can’t find FULLTEXT index matching the column list (indexes is set)
  23. What is the type this string? a:1:{s:2:”en”;}
  24. Extension mysqli is missing, phpmyadmin doesn’t work
  25. How can I do a migration in laravel 5.5?
  26. Best way to store IP in database?
  27. Which is fastest in PHP- MySQL or MySQLi?
  28. Is mysql_real_escape_string enough to Anti SQL Injection?
  29. How can I list data in sections by an ID, with a while loop in PHP? [closed]
  30. find in set in laravel ? example

Leave a Comment