如何优化缓慢的MySQL查询由于ORDER BY RAND（）？

问题描述：

所以我有这个相对简单的查询，从两个不同的MyISAM表基于一些标准拉一个随机行：

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.

根据我研究的结果，这是由于 ORDER BY RAND（）行显然在大表上运行非常慢。

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.

我真的很惊讶，MySQL没有内置的解决方案这个非常慢的 ORDER BY RAND（）问题在我看来，它是一个非常基本的需要，许多类型的应用程序需要选择一个随机的行。

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?

答

你只选择一行，我想你可以选择一个随机由LIMIT随机排列。示例：

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

如果您有：

SELECT * table  ORDER BY RAND( )  LIMIT 1;

更改为

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

$ randomvalue是从应用程序级别选择的随机值。

$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");

如何优化缓慢的MySQL查询由于ORDER BY RAND（）？

相关推荐