Mysql query taking unexpectedly long time to execute

[Noko]

Hello.

I have experienced a very weird problem with my myisam table with appr. 1M rows.

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 [B]desc[/B] 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 ~7000 rows 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

[javmedia]

I did a simple test on my P4 2x3GHZ with 2GB RAM

hardware/software setup

MyISAM MySQL 5.0.51
Windows XP SP3

using MySQL Query Browser local connection 127.0.0.1

tested on 2,679,888 records

ascending sorting in 8.9753 seconds - fetching took 0.0019s

SELECT id,f1 FROM `cohouse`.`ukco` ORDER BY f1 ASC LIMIT 10;

descending sorting in 4.8590 seconds - fetching took 0.0020s

SELECT id,f1 FROM `cohouse`.`ukco` ORDER BY f1 DESC LIMIT 10;

The alteration of sort speed between the two queries is likely due to automated caching of query simply reversing the sort order between calls 1 and 2.

the second time around the whole thing took 0.0003 seconds

[Noko]

8.9753 seconds to sort? Is that with index? Anyway, this is too long, and as profiler shows, my query sorts in 0.000008s but then is waiting 10 seconds in sending data state

[javmedia]

That is on the index.
The problem with this on a windows box also comes from the endtires not being put in, in the right order to start with.

The second time around as the query is cached, it is emensly faster.
The data retrieval is weird on the other hand. Are you running this on a local server or over a network? Is your firewall slowing it down? Or can you see if any other processes are accessing the database at the same time with the same user id?

[javmedia]

I did some reading on MySQL 5.1 (Maria) documentations for this in the help pages for Optimizing sorted queries.

-- If you want to increase ORDER BY speed, check whether you can get MySQL to use indexes rather than an extra sorting phase. If this is not possible, you can try the following strategies:

*

Increase the size of the sort_buffer_size variable.
*

Increase the size of the read_rnd_buffer_size variable.

Thats it, but they discussed nothing on the issue yuo have with slow transfer.

Could it be another setting in the ini files you need to validate?
As I said above, its difficult to know if you dont have access to the ini /conf settings yourself perhaps.

[Noko]

Both buffers are set to maximum integer value. And sorting is not the issue anyway.

I connect to server with ssh and run the query using their mysql client bundled with server, so you may think of it as of a local server. If queries from other processes were the cause of slowness, sorting by asc would be slow too; and I don't see how firewall can be related.

I guess this is a question for mysql staff after all. Too bad they're not replying.