CODEWITHSUNDEEP
mysqlperformance
Penman
Penman

Reputation: 81

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.

Table structure:

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,
....,
PRIMARY KEY (`id`),
....
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_persian_ci

Upvotes: 0

Views: 88

Answers (1)

Arulkumar
Arulkumar

Reputation: 13237

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

SELECT M1.*
FROM messages M1
LEFT JOIN messages M2 ON M2.phone = M1.phone AND M2.answer = 'SuccessUnSubscribed'
WHERE M1.answer = 'SuccessSubscribed' AND M2.phone IS NULL

Upvotes: 1

Related Questions