mysql order by rand 优化,如何优化缓慢的MySQL查询由于ORDER BY RAND（）？

So I have this relatively simple query to pull a random row based on a few criteria from two different MyISAM tables:

SELECT

table1 . * , table2 . *

FROM

Table1 AS table1

LEFT JOIN

Table2 AS table2

USING (

col1

)

WHERE

table1.col1 != '5324372'

AND

table1.col2 LIKE 'S'

AND (

table1.col3 LIKE 'I'

OR table1.col3 LIKE 'V-G'

)

AND (

table2.col1 = 'A'

OR table2.col2 = 'B'

)

ORDER BY RAND( )

LIMIT 1

...which at the beginning worked fine, but as my Database began to grow, now takes several seconds to execute.

All columns used above are indexed, so it's not an indexing problem.

From what I've researched, it's due to the ORDER BY RAND() line which apparently runs very slow on large tables.

I've seen some potential solutions to this problem, but they are quite cryptic and I could not wrap my head around any of them in relation to my query above.

I'm actually surprised that MySQL does not have a built-in solution to this very slow ORDER BY RAND() problem as it would seem to me like it is a very basic need for many types of applications needing to select a random row.

So how can I optimize my query above so that I get the same/similar effect without the long execution times?

解决方案

You are choosing only one row , I think you can choose one row randomly by LIMIT randomly. Example:

If you have:

SELECT * table ORDER BY RAND( ) LIMIT 1;

change it to

SELECT * table LIMIT $randomvalue ,$randomvalue + 1;

$randomvalue is a random value chosen from the application level.

update: the following answer is more clear than the above one.

//get the total number of rows

$result= mysql_query(" SELECT COUNT(*) AS total FROM `table` ");

$row = mysql_fetch_array($result);

$total=$row['total'];

//create random value from 1 to the total of rows

$randomvalue =rand(1,$total);

//get the random row

$result= mysql_query(" SELECT * FROM `table` limit $randomvalue,1");