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
