List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:December 9 2003 9:19am
Subject:Re: key is not used
View as plain text  
Hi,

I'm not saying I fully understand the MySQL optimizer, as
I never had to deal with it before... but:

Why should it use an index if the statement is returning ALL
rows? It might be faster to read them into memory ( 75853
isn't really much ) and sort them there instead of moving the
diskhead back and forth between the index and the data.

> i'm having a problem with mysql. when i put an index on a
> datetime column, it's never used.
>
> for example the
> sql: 'EXPLAIN SELECT * FROM actions ORDER BY datum'
>
> shows:
> table  type  possible_keys  key  key_len  ref  rows  Extra
> actions ALL NULL NULL NULL NULL 75853 Using filesort
>
> even if i use the
> sql: 'EXPLAIN SELECT * FROM actions USE INDEX (datum) ORDER BY datum'
>
> mysql doesn' use the index :-((

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

Thread
key is not usedCorin Langosch9 Dec
  • Re: key is not usedMartijn Tonies9 Dec
    • Re[2]: key is not usedCorin Langosch9 Dec
      • Re: key is not usedChuck Gadd9 Dec
  • update: key is not usedCorin Langosch9 Dec
    • Re: update: key is not usedEgor Egorov9 Dec
  • Re: Re[2]: key is not usedMartijn Tonies9 Dec