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