I have a very large table, where one of the columns is
color enum("red", "blue", "black")
The distribution of values is 90% black, 8% blue, and 2% red.
I want to be able to randomly select 10 rows matching a certain color from
the table.
Of course,
select * from my_table where color = "black" order by rand() limit 10
is very slow because the huge table has to be sorted before the first 10
rows are selected.
So what I did was create another column randomkey, wich is filled with
random integers in the range [0 .. 10^6]. Then I added an index (column,
randomkey).
The following query usually works:
select * from users where color = "black" and randomkey > [random value]
limit 10
However, the problem here is that only the first part of the index
(column, randomkey) is used sometimes.
So, if randomkey is 400000, mysql basically looks through 400000 rows
before it finds randomkey > 400000.
Would anybody have a clue about why this happens? I thought "limit" forced
the use of all available indices.
Thanks,
Andrey
---------------------------------------------------------------------
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