MySQL SELECT NOT IN 从 30K 行快速

Question

My DB table name_info has it 30K row & terms table 60K row, When select not in table, server CPU goes up.

我怎样才能最好地编写快速选择 1 行的查询？

SELECT slug FROM terms WHERE slug LIKE 'nm%' AND slug NOT IN 
(SELECT imdb_id FROM name_info) LIMIT 1

Answer 1

确保查询中使用的列已编入索引。

SELECT `slug`
FROM `terms` 
LEFT JOIN `name_info`
ON `slug` = `imdb_id`
WHERE slug LIKE 'nm%' 
AND `imdb_id` IS NULL
LIMIT 0,1;