Claus Reestrup wrote:
> Cant figure out why MySQL wont use index on a big table.
> Ok, the data is not evenly distributed which might be the problem.
> 
> Look here:
> 
> I have a table with 1 million records, with the following fields:
> IdUser, int
> X, int
> Y, int
> Z, int
> C, char(10)
> 
> no, varchars, text, or blobs.
> 
> IdUser is a user identity.
> As things are right now, only 3 users are registered.
> iduser=2, 34, 39
> User 39 owns 99.999 % of the data in the table.
> 
> When using EXPLAIN, Mysql tells me that when querying the table with IdUser=39, 
>MySQL will not use index.
> Querying the table with all other idusers than 39, causes MySQL to use index.
> 
> Has anyone of you seen this behavious before?
> 
> /Claus
> 
Yes.

It is even described in the manual:

"Note that in some cases MySQL will not use an index, even if one would
be available. Some of the cases where this happens are:

    * If the use of the index would require MySQL to access more than
30% of the rows in the table. (In this case a table scan is probably
much faster, as this will require us to do much fewer seeks.) Note that
if such a query uses LIMIT to only retrieve part of the rows, MySQL will
use an index anyway, as it can much more quickly find the few rows to
return in the result."

(see http://www.mysql.com/doc/en/MySQL_indexes.html)

Regards,
Joseph Bueno



---------------------------------------------------------------------
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

Reply via email to