MySQL Forums
Forum List  »  Optimizer & Parser

Re: Index evaluation
Posted by: Drei Bit
Date: July 24, 2007 05:24AM

Hi,

yeah would be the same here.

I am currently experimenting with complex subqueries. Back in the Days MySQL couldn't do some of them. So if I find something strange I try to narrow the problem down to the easiest reproducible query, derived from the complex query, where the same strangeness still persists.

On the other hand if I would also like to do something like distinct in here. Using the subquery he will (kind of) have to do the duplicate elimination in the beginning, which could be a lot cheaper depending on the query.

explain select * from t1 join ( select distinct id from t2 use index (ix_txt_id) where txt like 'Hall%' ) as y on y.id = t1.id;
/*
+----+-------------+------------+--------+---------------+-----------+---------+------+------+------------------------------+
| id | select_type | table      | type   | possible_keys | key       | key_len | ref  | rows | Extra                        |
+----+-------------+------------+--------+---------------+-----------+---------+------+------+------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL      | NULL    | NULL |   75 |                              |
|  1 | PRIMARY     | t1         | eq_ref | PRIMARY       | PRIMARY   | 4       | y.id |    1 |                              |
|  2 | DERIVED     | t2         | range  | ix_txt_id     | ix_txt_id | 768     | NULL |   80 | Using where; Using temporary |
+----+-------------+------------+--------+---------------+-----------+---------+------+------+------------------------------+
*/

explain select distinct t1.* from t1 join t2 as y on y.id = t1.id where y.txt like 'Hall%';
/*
+----+-------------+-------+--------+------------------------+---------+---------+------------------+------+------------------------------+
| id | select_type | table | type   | possible_keys          | key     | key_len | ref              | rows | Extra                        |
+----+-------------+-------+--------+------------------------+---------+---------+------------------+------+------------------------------+
|  1 | SIMPLE      | y     | range  | ix_id,ix_txt,ix_txt_id | ix_txt  | 768     | NULL             |   80 | Using where; Using temporary |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY                | PRIMARY | 4       | wadoku_test.y.id |    1 |                              |
+----+-------------+-------+--------+------------------------+---------+---------+------------------+------+------------------------------+
*/

Unfortunately the Explain command doesn't really well explain which order the things have and which the Operators are applied in what order. I personally would like an Operator based view better.

nice greetings
3Bit

Options: ReplyQuote


Subject
Views
Written By
Posted
3292
July 23, 2007 04:51PM
2279
July 23, 2007 05:18PM
2235
July 24, 2007 02:06AM
2236
July 24, 2007 04:33AM
2259
July 23, 2007 07:27PM
Re: Index evaluation
2215
July 24, 2007 05:24AM


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.