How can I make this WHERE NOT EXISTS query more efficient

Firstly, Tables

Surveys

CREATE TABLE `surveys` (
`survey_id` int(11) NOT NULL AUTO_INCREMENT,
`survey_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`survey_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


INSERT INTO `surveys` (`survey_id`, `survey_name`) VALUES
(1, 's1'),
(2, 's2');


Survey_responses

CREATE TABLE `survey_responses` (
`sr_id` int(10) NOT NULL AUTO_INCREMENT,
`sr_text` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`sr_tq_id` int(10) NOT NULL,
`sr_st_id` int(10) NOT NULL,
`sr_su_uid` int(10) NOT NULL,
PRIMARY KEY (`sr_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INSERT INTO `survey_responses` (`sr_id`, `sr_text`, `sr_tq_id`, `sr_st_id`, `sr_su_uid`) VALUES
(1, 'a', 3, 2, 3),
(2, 'b', 4, 2, 3);


Survey_topics

CREATE TABLE `survey_topics` (
`st_id` int(10) NOT NULL AUTO_INCREMENT,
`st_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`st_survey_id` int(10) NOT NULL,
PRIMARY KEY (`st_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INSERT INTO `survey_topics` (`st_id`, `st_name`, `st_survey_id`) VALUES
(1, 't1', 1),
(2, 't2', 1),
(3, 't3', 1),
(4, 't4', 2),
(5, 't5', 2),
(6, 't6', 2);


Survey_users

CREATE TABLE `survey_users` (
`su_id` int(10) NOT NULL AUTO_INCREMENT,
`su_s_id` int(10) NOT NULL,
`su_uid` int(10) NOT NULL,
PRIMARY KEY (`su_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INSERT INTO `survey_users` (`su_id`, `su_s_id`, `su_uid`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 2);


topic_questions

CREATE TABLE `topic_questions` (
`tq_id` int(11) NOT NULL AUTO_INCREMENT,
`tq_text` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`tq_st_id` int(10) NOT NULL,
PRIMARY KEY (`tq_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INSERT INTO `topic_questions` (`tq_id`, `tq_text`, `tq_st_id`) VALUES
(1, 'q1', 1),
(2, 'q2', 1),
(3, 'q3', 2),
(4, 'q4', 2);


Users

CREATE TABLE `users` (
`u_id` int(10) NOT NULL AUTO_INCREMENT,
`uname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`u_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INSERT INTO `users` (`u_id`, `uname`) VALUES
(1, 'Bob'),
(2, 'Gary');


Now the query,

SELECT * FROM `surveys`
INNER JOIN survey_users ON survey_users.su_s_id = surveys.survey_id
INNER JOIN survey_topics ON survey_topics.st_survey_id = surveys.survey_id
INNER JOIN topic_questions ON survey_topics.st_id = topic_questions.tq_st_id
WHERE NOT EXISTS (
SELECT * FROM survey_responses a WHERE a.sr_tq_id = topic_questions.tq_id AND a.sr_st_id = survey_topics.st_id AND a.sr_su_uid = survey_users.su_uid
)


Basically I am trying to get a list of surveys where even one user has missed a survey response even if that is for only 1 question for only 1 topic.

This query works well however is extremely slow on a database with thousands of surveys, topics, users , questions and responses.

Please can someone provide an query of doing this that will be quicker, I have read that using left join instead of WHERE NOT EXISTS is more efficient?

Appreciate the help guys, I have indexes in the actual database, this example does not have any indexes.

Thanks.

Continue reading...