MySQL Forums
Forum List  »  Performance

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       | |    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.

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

Written By
Re: Long execution time despite LIMIT 3
June 22, 2011 12:33AM

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.