MySQL SELECT NOT IN from 30K rows fast

Question

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

How can I best write a query that select 1 row fast?

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

Try joining terms to name_info with an outer join on name.info.imdb_id and terms.slug. The join will probably perform better than the subselect. — Lorna Mitchell, Apr 11 '17 at 07:25
Listen to @LornaMitchell. MySQL executes joins faster than the NOT IN sub-query construction. — jarlh, Apr 11 '17 at 07:31
Thank you, with @sloan-thrasher code work perfectly, exactly yours solution. — Parsa, Apr 11 '17 at 08:02

score 2 · Accepted Answer · answered Apr 11 '17 at 07:25

2

Make sure that the columns used in the query are indexed.

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

answered Apr 11 '17 at 07:25

Sloan Thrasher

score 0 · Answer 2 · answered Apr 11 '17 at 07:35

0

try to use not exists

SELECT slug FROM terms WHERE slug LIKE 'nm%' AND not exists
(SELECT 1 FROM name_info where imdb_id = slug) LIMIT 1

answered Apr 11 '17 at 07:35

Vecchiasignora

Thanks @Vecchiasignora, but behavior like my sql code – Parsa Apr 11 '17 at 07:47

2 Answers2