I'm looking for a way to optimize one SQL query that I have. I'm trying to get how many poems with a certain genre.
Query looks like this:
SELECT COUNT(*) FROM `poems` WHERE `id` IN ( SELECT `poem_id` FROM `poems_genres` WHERE `genre_title` = 'derision' ) AND `status` = 'finished';
It takes too long (about 6-10 seconds), because it can't use indexes (because of IN() I think?). Is there a way to rewrite this query in different way to get the same result faster?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `poems` ADD INDEX `poems_idx_status` (`status`); ALTER TABLE `poems_genres` ADD INDEX `poems_genres_idx_genre_title_poem_id` (`genre_title`,`poem_id`);
SELECT COUNT(*) FROM `poems` WHERE EXISTS ( SELECT 1 FROM `poems_genres` WHERE ( `poems_genres`.`genre_title` = 'derision' ) AND ( `poems`.`id` = `poems_genres`.`poem_id` ) ) AND `poems`.`status` = 'finished'