List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:March 15 2001 1:47pm
Subject:Re: should index usage depend on which table's columns are SELECTed ?
View as plain text  
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
Thread
should index usage depend on which table's columns are SELECTed ?Philip Lijnzaad14 Mar
  • should index usage depend on which table's columns are SELECTed ?Michael Widenius15 Mar
  • Re: should index usage depend on which table's columns are SELECTed ?Philip Lijnzaad15 Mar
    • Re: should index usage depend on which table's columns are SELECTed ?Michael Widenius15 Mar
  • Re: should index usage depend on which table's columns are SELECTed ?Philip Lijnzaad15 Mar
    • Re: should index usage depend on which table's columns are SELECTed ?Michael Widenius16 Mar