Penman Penman - 1 year ago 106
MySQL Question

Improve MySQL Nested select performance with join

I have seen many samples to improve mysql nested selects with joins, but i can't figure this out for this query:

SELECT * FROM messages WHERE answer = 'SuccessSubscribed' AND phone NOT IN
(SELECT phone FROM messages WHERE answer = 'SuccessUnSubscribed');

the query finds people who have subscribed but never unsubscribed.

target table:

CREATE TABLE `messages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`phone` varchar(12) COLLATE utf8_persian_ci NOT NULL,
`content` varchar(300) COLLATE utf8_persian_ci NOT NULL,
`flags` int(10) unsigned NOT NULL DEFAULT '0',
`answer` varchar(50) COLLATE utf8_persian_ci DEFAULT NULL,
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_persian_ci

Answer Source

Instead of the NOT IN, you can use LEFT JOIN with NULL check.

FROM messages M1
LEFT JOIN messages M2 ON = AND M2.answer = 'SuccessUnSubscribed'
WHERE M1.answer = 'SuccessSubscribed' AND IS NULL
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download