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 !
--
-------------------------------------------------------------------------
++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento
-------------------------------------------------------------------------
E S C R I B A I N F O R M A T I C A
*** http://javacoffe.blogspot.com ***
-------------------------------------------------------------------------
The only stupid question is the unasked one (somewhere in Linux's HowTo)
Linux registred user : #230601
-- ICQ : 1647350
$ look into "my eyes" Phone : +55 041 2106-1212
look: cannot open my eyes Fax : +55 041 3296-6640
-------------------------------------------------------------------------
Reply: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
