List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:March 14 2001 11:50pm
Subject:should index usage depend on which table's columns are SELECTed ?
View as plain text  
Hi!

>>>>> "Philip" == Philip Lijnzaad <lijnzaad@stripped> writes:

Philip> Dear all,
Philip> I'm sorry if this isn't the right forum, but I came across what looks like at
Philip> least an inconsistency, if not bug. 

Philip> On some database instance, the optimizer chooses to ignore a perfectly valid
Philip> index if I SELECT columns from the indexed table, but it has no trouble
Philip> (i.e. uses the right indexes) when SELECTing colums from other tables (which
Philip> are also indexed). This only happens for joins involving more than two
Philip> tables, and even if I put "USE INDEX (PRIMARY)" in the select. 

Philip> On another instance (same MySQL version, 3.23.25-beta, same machine and
Philip> disks, same schema, same indexes, different data), it works perfectly fine.

Philip> This is very puzzling; does anyone have any idea why this could be the case,
Philip> so that I can avoid it in the future? 

Philip> Many thanks in advance,

Yes, the optimizer may well avoid using an index, if it things the end
result will be faster by not using it.

Without the EXPLAIN output and a 'show create table' of your table's I
can't however comment on why MySQL doesn't use the index you expect it
to use.

Regards,
Monty

*************** Warning commercial signature follows **********
If you like MySQL's concept of a free [speech] database and
free [vodka] advice, you should at least CONSIDER supporting us
so we can afford to keep this service up. http://www.mysql.com/
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