Dear all,
I have the following query:
SELECT distinct pf.*
FROM homo_sapiens_lite_120.gene lg,
homo_sapiens_lite_120.gene_prot lgp,
homo_sapiens_core_120.protein_feature pf
WHERE lg.chr_name = 'chr22'
AND lg.gene = lgp.gene
AND lgp.translation_id = pf.translation
However, EXPLAIN on this says:
+-------+------+----------------------------+----------------+---------+----------------+---------+----------------------+
| table | type | possible_keys | key | key_len | ref
| | rows | Extra |
+-------+------+----------------------------+----------------+---------+----------------+---------+----------------------+
| pf | ALL | translation | NULL | NULL | NULL
| | 2316340 | Using temporary |
| lgp | ref | gene,gene_2,translation_id | translation_id | 4 |
|pf.translation | 19 | where used; Distinct |
| lg | ref | gene,chr,chr_start | gene | 4 | lgp.gene
| | 19 | where used; Distinct |
+-------+------+----------------------------+----------------+---------+----------------+---------+----------------------+
This is puzzling, because pf.translation should be a perfectly valid index?
(also see the 'SHOW CREATE TABLE's below).
Is this a bug or a feature ?
What's worse, I can't persuade MySQL (server version 3.23.32 on TrueUnix) to
use the index by adding ``USE INDEX(translation)'' as
SELECT distinct pf.*
FROM homo_sapiens_lite_120.gene lg,
homo_sapiens_lite_120.gene_prot lgp,
homo_sapiens_core_120.protein_feature pf USE INDEX(translation)
WHERE lg.chr_name = 'chr22'
AND lg.gene = lgp.gene
AND lgp.translation_id = pf.translation
Does the parser/optimizer routinely ignore the 'USE INDEX(name)'-hints ?
(I've seen this before and find it a bit annoying, really).
Any help much appreciated.
Philip
mysql> show create table protein_feature;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
|
|
|
|
|
| |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| protein_feature | CREATE TABLE `protein_feature` (
`id` int(10) unsigned NOT NULL auto_increment,
`translation` varchar(40) NOT NULL default '',
`seq_start` int(10) NOT NULL default '0',
`seq_end` int(10) NOT NULL default '0',
`analysis` int(10) unsigned NOT NULL default '0',
`hstart` int(10) NOT NULL default '0',
`hend` int(10) NOT NULL default '0',
`hid` varchar(40) NOT NULL default '',
`score` double(16,4) NOT NULL default '0.0000',
`evalue` varchar(20) default NULL,
`perc_id` int(10) default NULL,
PRIMARY KEY (`id`),
KEY `translation`(`translation`),
KEY `hid_index`(`hid`)
) TYPE=MyISAM |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> show create table homo_sapiens_lite_120.gene_prot;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
|
|
|
|
|
|
|
| |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| gene_prot | CREATE TABLE `gene_prot` (
`gene` int(10) unsigned NOT NULL default '0',
`translation_id` int(10) unsigned NOT NULL default '0',
`translation` char(40) NOT NULL default '',
`prints` char(40) default NULL,
`prosite` char(40) default NULL,
`pfam` char(40) default NULL,
`coil` char(40) default NULL,
`low_complexity` char(40) default NULL,
`signal_peptide` char(40) default NULL,
`transmembrane` char(40) default NULL,
`interpro` char(40) default NULL,
`short_description` char(255) default NULL,
`description` char(255) default NULL,
KEY `gene`(`gene`),
KEY `prosite`(`prosite`,`gene`),
KEY `prints`(`prints`,`gene`),
KEY `pfam`(`pfam`,`gene`),
KEY `interpro`(`interpro`,`gene`),
KEY `gene_2`(`gene`),
KEY `translation_id`(`translation_id`)
) TYPE=MyISAM |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql>
--
The mail transport agent is not liable for any coffee stains in this message
-----------------------------------------------------------------------------
Philip Lijnzaad, [EMAIL PROTECTED] European Bioinformatics Institute,rm A2-08
+44 (0)1223 49 4639 Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax) Cambridgeshire CB10 1SD, GREAT BRITAIN
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
