MySQL Forums
Forum List  »  Quality Assurance

Query takes unexpectedly long time.
Posted by: Andrey Osenenko
Date: March 03, 2009 12:20AM

Hello.

We have a myisam table with appr. 1M rows, and have experienced a very weird problem.

Query
select num,subnum from a where trip='!mOWjKAWAII' order by timestamp asc limit 10;
executes immediately, which is just as expected.

But with a slight modification:
select num,subnum from a where trip='!mOWjKAWAII' order by timestamp desc limit 10;
it takes whopping 10 seconds to execute. The worst thing is that the precious time is wasted on something absolutely unrelated -- Sending data. Here's an output of profiler:

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000089 |
| checking permissions |  0.000010 |
| Opening tables       |  0.000014 |
| System lock          |  0.000008 |
| Table lock           |  0.000011 |
| init                 |  0.000028 |
| optimizing           |  0.000011 |
| statistics           |  0.000338 |
| preparing            |  0.000019 |
| executing            |  0.000005 |
| Sorting result       |  0.000008 |
| Sending data         | 10.743997 |
| end                  |  0.000017 |
| query end            |  0.000005 |
| freeing items        |  0.000027 |
| logging slow query   |  0.000005 |
| logging slow query   |  0.000004 |
| cleaning up          |  0.000006 |
+----------------------+-----------+

This happens when I run query from console manually and when I execute it from perl cgi script in lighthttpd.

If that matters, the are ~700 columns matching the where condition, and output of explain for both queries is same:

mysql> explain select num,subnum from a where trip='!mOWjKAWAII' order by timestamp desc limit 10;
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key             | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-------------+
|  1 | SIMPLE      | a     | index | trip_index    | timestamp_index | 5       | NULL | 3420 | Using where |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select num,subnum from a where trip='!mOWjKAWAII' order by timestamp asc limit 10;
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key             | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-------------+
|  1 | SIMPLE      | a     | index | trip_index    | timestamp_index | 5       | NULL | 3420 | Using where |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

Server version: 5.1.30 FreeBSD port: mysql-server-5.1.30

Options: ReplyQuote


Subject
Views
Written By
Posted
Query takes unexpectedly long time.
4618
March 03, 2009 12:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.