Hi!
>>>>> "Philip" == Philip Lijnzaad <lijnzaad@stripped> writes:
<cut>
Philip> ah, but the point is the query is infinitely slower if it doesn't use the
Philip> index. And in general, it surely should not depend on the projections
Philip> (i.e. the headers of the result), only on the restriction (the WHERE
Philip> clause), yet that is exactly what I'm seeing.
Michael> Without the EXPLAIN output and a 'show create table' of your table's I
Michael> can't however comment on why MySQL doesn't use the index you expect it
Michael> to use.
<cut>
Philip> EXPLAIN
Philip> SELECT sgp.*
Philip> FROM static_golden_path sgp USE INDEX(PRIMARY),
Philip> exon e,
Philip> exon_transcript et
Philip> WHERE
Philip> et.transcript = 'ENST00000023193'
Philip> AND sgp.raw_id = e.contig
Philip> AND e.id = exon;
Philip> does not. I have appended all the details as an attachment; feel free to
Philip> ignore it :-) Cheers,
<cut>
Philip> EXPLAIN
Philip> SELECT e.*
Philip> FROM static_golden_path sgp USE INDEX(PRIMARY),
Philip> exon e,
Philip> exon_transcript et
Philip> WHERE
Philip> et.transcript = 'ENST00000023193'
Philip> AND sgp.raw_id = e.contig
Philip> AND e.id = exon;
Philip>
> +-------+------+------------------------------------------+------------------+---------+----------+------+-------------+
Philip> | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
Philip>
> +-------+------+------------------------------------------+------------------+---------+----------+------+-------------+
Philip> | et | ref | PRIMARY,idx1,exon_index,transcript_index | transcript_index |
> 40 | const | 8 | where used |
Philip> | e | ref | idx1,contig_index,PRIMARY | idx1 |
> 40 | et.exon | 1829 | |
Philip> | sgp | ref | PRIMARY | PRIMARY |
> 4 | e.contig | 2920 | Using index |
Philip>
> +-------+------+------------------------------------------+------------------+---------+----------+------+-------------+
Check the 'rows' column. According to this the 'weight' of the query
is about 8*1829*2920 = 42 M rows
Philip> EXPLAIN
Philip> SELECT sgp.*
Philip> FROM static_golden_path sgp USE INDEX(PRIMARY),
Philip> exon e,
Philip> exon_transcript et
Philip> WHERE
Philip> et.transcript = 'ENST00000023193'
Philip> AND sgp.raw_id = e.contig
Philip> AND e.id = exon;
Philip>
> +-------+-------+------------------------------------------+------------------+---------+------------+--------+------------+
Philip> | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
Philip>
> +-------+-------+------------------------------------------+------------------+---------+------------+--------+------------+
Philip> | sgp | ALL | PRIMARY | NULL |
> NULL | NULL | 292016 | |
Philip> | e | ref | idx1,contig_index,PRIMARY | contig_index |
> 4 | sgp.raw_id | 19 | |
Philip> | et | range | PRIMARY,idx1,exon_index,transcript_index | transcript_index |
> 40 | NULL | 8 | where used |
Philip>
> +-------+-------+------------------------------------------+------------------+---------+------------+--------+------------+
And the weight of this is just: 292016*19*8 = 44 M rows. Using disk
scanning this is reduced to 'just' 4M rows, as you can read about 10
rows while scanning at the same time you read one row with indexing.
Proposed fix:
Change the tables to MyISAM tables and run 'analyze' on the tables.
This will give MySQL better information about the index distribution
and should help it choose the right indexes to use.
If you don't want to use ISAM, the other solution is to add a separate
index in 'sgp.ref'; The reason is that ISAM only supports statistics
for the whole key, not for the key parts as MyISAM does.
Regards,
Monty
