* Claus Reestrup > 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?
<URL: http://www.mysql.com/doc/en/MySQL_indexes.html > >From the first paragraph: "If the table has an index for the columns in question, MySQL can quickly get a position to seek to in the middle of the datafile without having to look at all the data. If a table has 1000 rows, this is at least 100 times faster than reading sequentially. Note that if you need to access almost all 1000 rows it is faster to read sequentially because we then avoid disk seeks." In other words: mysql does not use the index because it is faster to do a full table scan in this case. -- Roger --------------------------------------------------------------------- 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
