List:General Discussion« Previous MessageNext Message »
From:Dyego Souza Dantas Leal Date:April 20 2006 5:37pm
Subject:MySQL Optimization error ?
View as plain text  
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: dyego@stripped 

Thread
MySQL Optimization error ?Dyego Souza Dantas Leal20 Apr
  • Re: MySQL Optimization error ?David Griffiths20 Apr