This isn't a bug, it's a missing feature. The parent query won't use indexes,
just the subquery.
There's been a lot of complaints about how it renders sub queries less than
useful.
I seem to remember that it might be fixed in 5.1, but I'm not a reliable source
of info on this.
David
Dyego Souza Dantas Leal wrote:
I have a good question, the MySQL Optimizer is broker ? see the querys:
mysql> explain select * from an where an_id in (100,200);
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | an | range | An_Id | An_Id | 4
| | 2 | Using where |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)
GREAT !!! the MySQL uses primary index to search the rows... BUt , if
i'm using subselect the response is not good.
mysql> explain select * from an where an_id in (select an_id from an
where an_id between 100 and 103);
+----+--------------------+-------+-----------------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+---------------+-------+---------+------+------+--------------------------+
| 1 | PRIMARY | an | ALL |
| | | | 2489 | Using where |
| 2 | DEPENDENT SUBQUERY | an | unique_subquery | An_Id |
An_Id | 4 | func | 1 | Using index; Using where |
+----+--------------------+-------+-----------------+---------------+-------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)
mysql>
this is very slow... the MAX return in 0.001s , but the IN CLAUSE not
use the PRIMARY INDEX , this causes FULL TABLE SCAN !!!
Optimizer is Broken ?
MySQL Version: 5.0.19-pro
Plataform: Windows or Linux box (debian kernel 2.6.14-1)
Memory : 1 GB of RAM
Table Type: InnoDB
Tnks in advance !
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
