MySQL 查询运行缓慢

Question

我让这个查询工作了，但它真的很慢。

我想要这个查询:1个随机(id,word,meaning)和1个随机不正确的任何其他单词含义。如何通过查询获得更好的性能？

$offset_result = mysql_query("SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM words WHERE APPROVAL=1 AND PERIOD<".$_SESSION['statistics']['d']." OR ( PERIOD=".$_SESSION['statistics']['d']."  AND WEEK<=".$_SESSION['statistics']['h'].")");
    $offset_row = mysql_fetch_object($offset_result);
    $offset = $offset_row->offset;  
    $words = mysql_query("SELECT ID,WORD,MEANING,
    (SELECT MEANING FROM words WHERE ID!=w.ID AND APPROVAL=1 AND WORD!=w.WORD AND NOT FIND_IN_SET(w.MEANING, OTHER_MEANING) AND ID >= (SELECT FLOOR( MAX(ID) * RAND()) FROM words) ORDER BY ID LIMIT 1) AS INCORRECT 
    FROM words w 
    LIMIT $offset, 1");

这是表格布局

CREATE TABLE words (
    ID int(11) NOT NULL AUTO_INCREMENT,
    APPROVAL tinyint(1) NOT NULL DEFAULT '1',
    WORD varchar(255) COLLATE utf8_turkish_ci NOT NULL,
    MEANING varchar(255) COLLATE utf8_turkish_ci NOT NULL,
    OTHER_MEANING varchar(255) COLLATE utf8_turkish_ci NOT NULL,
    PERIOD tinyint(1) NOT NULL,
    WEEK tinyint(2) NOT NULL,
    PRIMARY KEY (ID),
    KEY APPROVAL (APPROVAL) ) 
    ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=769 ;

Answer 1

最大的问题是 != 和 not in 子句。如果您重写此代码以不使用这些函数，您的情况会更好。

guide

Summary

MySQL can optimize all three methods to do a sort of NESTED LOOPS ANTI JOIN.

It will take each value from t_left and look it up in the index on t_right.value. In case of an index hit or an index miss, the corresponding predicate will immediately return FALSE or TRUE, respectively, and the decision to return the row from t_left or not will be made immediately without examining other rows in t_right.

However, these three methods generate three different plans which are executed by three different pieces of code. The code that executes EXISTS predicate is about 30% less efficient than those that execute index_subquery and LEFT JOIN optimized to use Not exists method.

That’s why the best way to search for missing values in MySQL is using a LEFT JOIN / IS NULL or NOT IN rather than NOT EXISTS.