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 1083 Joachim Berger 06/19/2011 12:47PM
Re: Long execution time despite LIMIT 3 469 Joachim Berger 06/19/2011 12:54PM
Re: Long execution time despite LIMIT 3 445 Rick James 06/20/2011 06:33PM
Re: Long execution time despite LIMIT 3 433 Joachim Berger 06/20/2011 11:44PM
Re: Long execution time despite LIMIT 3 453 Joachim Berger 06/21/2011 02:06AM
Re: Long execution time despite LIMIT 3 454 Rick James 06/21/2011 07:23AM
Re: Long execution time despite LIMIT 3 404 Joachim Berger 06/21/2011 07:28AM
Re: Long execution time despite LIMIT 3 486 Joachim Berger 06/21/2011 10:33AM
Re: Long execution time despite LIMIT 3 533 Rick James 06/21/2011 10:08PM
Re: Long execution time despite LIMIT 3 611 Joachim Berger 06/22/2011 12:33AM
Re: Long execution time despite LIMIT 3 485 Rick James 06/22/2011 07:26PM
Re: Long execution time despite LIMIT 3 436 Joachim Berger 06/22/2011 11:45PM
Re: Long execution time despite LIMIT 3 393 Rick James 06/23/2011 08:53AM
Re: Long execution time despite LIMIT 3 509 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.