Skip navigation links

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


Advanced Search

Re: Long execution time despite LIMIT 3
Posted by: Joachim Berger ()
Date: June 22, 2011 12:33AM

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.

Options: ReplyQuote


Subject Views Written By Posted
Long execution time despite LIMIT 3 1158 Joachim Berger 06/19/2011 12:47PM
Re: Long execution time despite LIMIT 3 489 Joachim Berger 06/19/2011 12:54PM
Re: Long execution time despite LIMIT 3 469 Rick James 06/20/2011 06:33PM
Re: Long execution time despite LIMIT 3 456 Joachim Berger 06/20/2011 11:44PM
Re: Long execution time despite LIMIT 3 479 Joachim Berger 06/21/2011 02:06AM
Re: Long execution time despite LIMIT 3 482 Rick James 06/21/2011 07:23AM
Re: Long execution time despite LIMIT 3 419 Joachim Berger 06/21/2011 07:28AM
Re: Long execution time despite LIMIT 3 504 Joachim Berger 06/21/2011 10:33AM
Re: Long execution time despite LIMIT 3 557 Rick James 06/21/2011 10:08PM
Re: Long execution time despite LIMIT 3 657 Joachim Berger 06/22/2011 12:33AM
Re: Long execution time despite LIMIT 3 509 Rick James 06/22/2011 07:26PM
Re: Long execution time despite LIMIT 3 459 Joachim Berger 06/22/2011 11:45PM
Re: Long execution time despite LIMIT 3 419 Rick James 06/23/2011 08:53AM
Re: Long execution time despite LIMIT 3 530 Joachim Berger 06/24/2011 02:51AM


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.