MySQL - 带有内连接的 SQL 查询花费太长时间

Question

我想优化以下查询。在我的开发服务器上执行它需要 150-200 秒(LIMIT 10)。我无法在生产服务器上运行它，因为执行时间有限。

SELECT 
intensive.id AS intensive_id, 
intensive.module_id AS intensive_module_id,  
w.id AS w_id, 
intensive.translation, 
w.name_basic_lang, 
w.image_name, 
w.image_number, 
w.image_ext 
FROM mk_int_module_multiwordssentences intensive
INNER JOIN mk_words w ON intensive.translation= w.name_basic_lang
WHERE w.image_number <> '' AND w.image_number <> -1 AND intensive.img_name IS NULL
GROUP BY intensive.id

Answer 1

试试这个:

SELECT 
...
FROM mk_int_module_multiwordssentences intensive
INNER JOIN mk_words w ON intensive.translation = w.name_basic_lang
and w.image_number <> '' AND w.image_number <> -1 AND intensive.img_name IS NULL 
Group by ...