Exists BUG in "IN" ?

[Dyego Souza Dantas Leal]

Hello Guys,


I'm using the 5.0.12 version of MySQL PRO on AMD64 3000+ with 1 GB of 
ram and using the InnoDB Tables..

Here is my table:

CREATE TABLE `svcs_filecontrol` (
  `fc_id` int(10) unsigned NOT NULL auto_increment,
  `fc_us_id_lockby` int(10) unsigned default NULL,
  `fc_lbl_id` int(10) unsigned NOT NULL default '0',
  `fc_nome` varchar(255) NOT NULL default '',
  `fc_package` text NOT NULL,
  `fc_arquivo` longblob NOT NULL,
  `fc_versao` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`fc_id`),
  KEY `fc_us_id_lockby` (`fc_us_id_lockby`),
  KEY `fc_lbl_id` (`fc_lbl_id`),
  KEY `fc_nome` (`fc_nome`),
  KEY `fc_lbl_nome_pacote_versao` 
(`fc_lbl_id`,`fc_nome`,`fc_package`(500),`fc_versao`),
  KEY `fc_versao` (`fc_versao`),
  KEY `fc_pacote_nome` (`fc_package`(255),`fc_nome`),
  CONSTRAINT `svcs_filecontrol_ibfk_1` FOREIGN KEY (`fc_lbl_id`) 
REFERENCES `svcs_label` (`lbl_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> select count(*) from svcs_filecontrol;
+----------+
| count(*) |
+----------+
|     1147 |
+----------+
1 row in set (0.35 sec)

mysql>


Here is the Select:

mysql> select f1.fc_package from svcs_filecontrol f1 where f1.fc_id in (
    -> *select max(f2.fc_id) from svcs_filecontrol f2
    -> where f2.fc_lbl_Id = 1
    -> group by f2.fc_package
    -> order by f2.fc_versao desc*)
    -> ;
+----------------------------------------------------+
| fc_package                                         |
+----------------------------------------------------+
| br.com.escriba                                     |
| br.com.escriba.compartilhado.ejb                   |
| br.com.escriba.compartilhado.ejb.interfaces        |
| br.com.escriba.components.actions                  |
| br.com.escriba.components.config                   |
| br.com.escriba.components.editor                   |
| br.com.escriba.components.framework                |
| br.com.escriba.components.imageviewer              |
| br.com.escriba.components.interfacereport          |
| br.com.escriba.components.pesquisa                 |
| br.com.escriba.components.table                    |
| br.com.escriba.components.wordprocessor            |
| br.com.escriba.components.wordprocessor.multipage  |
| br.com.escriba.components.wordprocessor.rtf        |
| br.com.escriba.components.wordprocessor.rtf.app    |
| br.com.escriba.components.wordprocessor.rtf.reader |
| br.com.escriba.components.wordprocessor.rtf.view   |
| br.com.escriba.components.wordprocessor.rtf.writer |
| br.com.escriba.images.components                   |
| br.com.escriba.testes                              |
| br.com.escriba.util                                |
| br.com.escriba.util.xml                            |
| org.syntax.jedit                                   |
| org.syntax.jedit.tokenmarker                       |
| br.com.escriba.compartilhado                       |
| tests                                              |
| tests.compartilhado                                |
| tests.compartilhado.ejb                            |
| br.com.escriba.components                          |
+----------------------------------------------------+
29 rows in set (7.77 sec)

mysql>


The problem is ... 7.77 sec to a simple select :( , is a bug ? because 
if i run the SUB-SELECT separated of query , this run faster... look:


mysql> *select max(f2.fc_id) from svcs_filecontrol f2 where f2.fc_lbl_Id 
= 1 group by f2.fc_package order by f2.fc_versao desc;*
+---------------+
| max(f2.fc_id) |
+---------------+
|           243 |
|             2 |
|           235 |
.....
+---------------+
29 rows in set (0.00 sec)

mysql>


The DESC of this command is:


mysql> desc select f1.fc_package from svcs_filecontrol f1 where f1.fc_id 
in (select max(f2.fc_id) from svcs_filecontrol f2 where f2.fc_lbl_Id = 1 
group by f
2.fc_package order by f2.fc_versao desc);
+----+--------------------+-------+------+-------------------------------------+-----------+---------+-------+------+----------------------------------------------+
| id | select_type        | table | type | 
possible_keys                       | key       | key_len | ref   | rows 
| Extra                                        |
+----+--------------------+-------+------+-------------------------------------+-----------+---------+-------+------+----------------------------------------------+
|  1 | PRIMARY            | f1    | ALL  | 
NULL                                | NULL      | NULL    | NULL  | 2440 
| Using where                                  |
|  2 | DEPENDENT SUBQUERY | f2    | ref  | 
fc_lbl_id,fc_lbl_nome_pacote_versao | fc_lbl_id | 4       | const |  435 
| Using where; Using temporary; Using filesort |
+----+--------------------+-------+------+-------------------------------------+-----------+---------+-------+------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql>




Is a bug ?


Tnks in advance...



MySQL,InnoDB,heeeeeeeeeeeeeeeelppp

-- 



-------------------------------------------------------------------------
++  Dyego Souza Dantas Leal   ++           Dep. Desenvolvimento  
-------------------------------------------------------------------------
               E S C R I B A   I N F O R M A T I C A
-------------------------------------------------------------------------
The only stupid question is the unasked one (somewhere in Linux's HowTo)
Linux registred user : #230601
--                                        ICQ   : 1647350
$ look into "my eyes"                     Phone : [phone]    
look: cannot open my eyes                 Fax   : [phone]    
-------------------------------------------------------------------------
             Reply: dyego@stripped