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 : +55 041 2106-1212
look: cannot open my eyes Fax : +55 041 296 -6640 -------------------------------------------------------------------------
Reply: [EMAIL PROTECTED]
