Hi!
>>>>> "Ulf" == Ulf Harnhammar <ulf@stripped> writes:
Ulf> On Sun, 27 Jan 2002, Michael Widenius wrote:
>> One way to do this is to do as follows:
>> SELECT something FROM sometable WHERE somevar=somevalue ORDER BY
>> RAND() LIMIT 10
Ulf> OK. I still think that my extended LIMIT syntax would be a good idea,
Ulf> because ORDER BY RAND() does not seem to be very fast.
If you want to have a true random order of rows, MySQL must first
find all the rows that matches the where clause.
Compared to that, the speed of ORDER BY and LIMIT is normally neglectable!
(MySQL do also optimize ORDER BY combined with LIMIT fairly well)
Ulf> I tried adding a field that numbers all instances of a certain ID (this
Ulf> probably breaks some normalization rule). This way I could simulate what
Ulf> things would be like with an extended LIMIT syntax. This SQL statement:
Ulf> SELECT varWord FROM dictionaryword WHERE intTypeID='11' AND
Ulf> (intTypeCounter<26)
Ulf> executed a lot faster than:
Ulf> SELECT varWord FROM dictionaryword WHERE intTypeID='11' ORDER BY RAND()
Ulf> LIMIT 25
The queries are not comparable as the second query will have to first
retrieve all the rows, not just those that has been previously marked.
Another thing is that the second query will every time return a
different set of rows, which is probably what you want in the first
place.
Ulf> Thus, I still think it would be a good idea to be able to say:
Ulf> SELECT COUNT(*) AS greve FROM dictionaryword WHERE intTypeID='11'
Ulf> and then:
Ulf> SELECT varWord FROM dictionaryword WHERE intTypeID='11' LIMIT number1,1,
Ulf> number2,1,number3,1,number4,1,..
Which would in almost all cases be much slower than doing ORDER BY RAND.
Regards,
Monty
