MySQL Forums :: Performance :: Long execution time despite LIMIT 3

Hi Rick,

your query looks interesting - I am still playing with it. Did you change any of the indexes, or are those still

PRIMARY KEY  (`id`), KEY `sort1` (`thread`,`id`), KEY `idx_r` (`id_user_r`), KEY `idx_s` (`id_user_s`)

? That would be great, because we wouldn't have to ALTER anything.

When I EXPLAIN your query, I get:

+----+--------------+------------+--------+---------------+---------+---------+------+------+---------------------------------+
| id | select_type  | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra                           |
+----+--------------+------------+--------+---------------+---------+---------+------+------+---------------------------------+
|  1 | PRIMARY      | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL |    6 | Using temporary; Using filesort |
|  1 | PRIMARY      | m          | eq_ref | PRIMARY       | PRIMARY | 8       | x.id |    1 |                                 |
|  2 | DERIVED      | messages   | index  | sort1,idx_s   | PRIMARY | 8       | NULL |    2 | Using where                     |
|  3 | UNION        | messages   | index  | sort1,idx_r   | PRIMARY | 8       | NULL |    2 | Using where                     |
| NULL | UNION RESULT | <union2,3> | ALL    | NULL          | NULL    | NULL    | NULL | NULL |                                 |
+----+--------------+------------+--------+---------------+---------+---------+------+------+---------------------------------+
5 rows in set (0.03 sec)

It doesn't literally say "Using index" though. I can see that it's using the PRIMARY key everywhere which seems good. What about the "Using filesort" for the result? Too small to worry about? And somehow, I still don't get the whole idea behind it. Why does making the UNION part of a JOIN allow for a completely different usage of the keys? When I EXPLAIN only the inner UNION, I get "Using intersect(sort1,idx_s); Using where; Using filesort". How come this goes away?

Concerning the lagging, I should maybe open a new topic, but this somehow seems to fit here, because it is exactly this query we're talking about. If this thing (or any other SELECT query on the messages table) runs for a minute, the slaves begin to lag. The table is heavily written also. Is this the reason?

Thank you. Appreciating your help very much.
Ben


EDIT: Your query almost takes equally long. Even the EXPLAIN takes about 30 seconds. What do think about maybe changing/adding some indexes? I know, this depends on all other queries on that table, too, but that's basically easy stuff.

EDIT 2: Just tested it in production. The servers were clogged with this query. There were dozens in the processlist, taking two-digit times each...



Edited 3 time(s). Last edit at 06/22/2011 01:58AM by Joachim Berger.