Slow Query With Subqueries

Question

I'm still rather new to doing more than just simple selects in mysql and I'm trying to optimize a rather large query. I was wondering if anyone who's been doing this for a while has any guidance on how to make this query run faster as it presently takes about 7 seconds to load with about 204k rows in the issue_prices table and about the same amount in the issues table. This is for a website so that is too long for a single resource.

Here is the query:

SELECT issues.id, issues.story_title, issues.number, issues.variant, issues.first_appearance_of, issues.cover_date, issue_types.name AS issue_type_name, (issues.price_guide_id_release = users.price_guide_id) AS new, (issues.updated > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS recentlyUpdated, issues.created, issues.updated, issues.title_id, issue_prices.price, previousIP.price AS previous_price, SUM(favorites_have.amount) AS sum_amount, COUNT(favorites_want.amount) AS want FROM issues
LEFT JOIN titles ON titles.id = issues.title_id
LEFT JOIN users ON users.id = ?
LEFT JOIN favorites ON favorites.issue_id = issues.id AND favorites.user_id = users.id
LEFT JOIN favorites favorites_have ON favorites_have.issue_id = issues.id AND favorites_have.user_id = users.id AND favorites_have.favorite_type_id = 1
LEFT JOIN favorites favorites_want ON favorites_want.issue_id = issues.id AND favorites_want.user_id = users.id AND favorites_want.favorite_type_id = 2
LEFT JOIN (SELECT issue_id, MAX(issue_prices.price_guide_id) AS max_pg_id, MIN(issue_prices.price_guide_id) AS price_guide_release_id FROM issue_prices
    LEFT JOIN issues ON issues.id = issue_prices.issue_id
    LEFT JOIN users ON users.id = ? AND issue_prices.price_guide_id <= users.price_guide_id GROUP BY issue_id) maxIP ON maxIP.issue_id = issues.id
LEFT JOIN issue_prices ON issue_prices.issue_id = issues.id AND issue_prices.price_guide_id = maxIP.max_pg_id
LEFT JOIN (SELECT issue_id, MAX(issue_prices.price_guide_id) AS max_pg_id FROM issue_prices
    LEFT JOIN users ON users.id = ? AND issue_prices.price_guide_id <= users.price_guide_id
    WHERE issue_prices.id NOT IN
        (SELECT ips.id FROM issue_prices ips
            INNER JOIN (SELECT issue_prices.issue_id, MAX(issue_prices.price_guide_id) AS max_pg_id FROM issue_prices
                LEFT JOIN users ON users.id = ? AND issue_prices.price_guide_id <= users.price_guide_id GROUP BY issue_id) AS x
        WHERE ips.issue_id = x.issue_id AND ips.price_guide_id = x.max_pg_id)
    GROUP BY issue_id) prevMaxIP ON prevMaxIP.issue_id = issues.id
LEFT JOIN issue_prices previousIP ON previousIP.issue_id = issues.id AND previousIP.price_guide_id = prevMaxIP.max_pg_id
LEFT JOIN issue_types ON issue_types.id = issues.issue_type_id
WHERE titles.deleted = FALSE AND issues.deleted = FALSE AND issue_prices.price_guide_id <= users.price_guide_id AND (issues.price_guide_id_release = users.price_guide_id OR issues.updated > DATE_SUB(NOW(), INTERVAL 1 MONTH) OR issue_prices.price_guide_id = users.price_guide_id)
GROUP BY issues.id, issue_prices.price, titles.name
ORDER BY issues.updated DESC, issues.created DESC, issue_prices.id DESC, titles.name ASC
LIMIT 0, 25

The intensive part is getting the most recent price (MaxIP) that is less than or equal to the users price_guide_id and the one previous to that (PrevMaxIp) which is not necessarily the price guide that is immediately previous to the most recent price (MaxIP - 1) as prices are only inserted if they have changed.

Thanks to anyone who has some insight into how to optimize this query :)

EDIT:

Attempt at converting the NOT IN (SELECT... to a JOIN:

SELECT issue_id, MAX(issue_prices.price_guide_id) AS max_pg_id FROM issue_prices LEFT JOIN users ON users.id = ? AND issue_prices.price_guide_id <= users.price_guide_id LEFT JOIN (SELECT ips.id FROM issue_prices ips INNER JOIN ( SELECT issue_prices.issue_id, MAX(issue_prices.price_guide_id) AS max_pg_id FROM issue_prices  LEFT JOIN users ON users.id = ? AND issue_prices.price_guide_id <= users.price_guide_id  GROUP BY issue_id ) AS x WHERE ips.issue_id = x.issue_id AND ips.price_guide_id = x.max_pg_id) MaxIP WHERE MaxIP.id IS NULL GROUP BY issue_id

Here's what I believe to be the relevant SHOW CREATE TABLE...s:

Issues:

CREATE TABLE `issues` (
 `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
 `title_id` bigint(11) unsigned NOT NULL,
 `created` datetime DEFAULT CURRENT_TIMESTAMP,
 `updated` datetime DEFAULT NULL,
 `deleted` tinyint(1) NOT NULL DEFAULT '0',
 `issue_type_id` bigint(11) unsigned NOT NULL DEFAULT '1',
 `number` varchar(20) DEFAULT NULL,
 `variant` varchar(100) NOT NULL DEFAULT '',
 `story_title` varchar(100) DEFAULT NULL,
 `cover_date` varchar(100) DEFAULT NULL,
 `cover_price` decimal(10,2) DEFAULT NULL,
 `first_appearance_of` varchar(255) DEFAULT NULL,
 `price_guide_id_release` bigint(11) unsigned NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`),
 KEY `FK_issues_titles` (`title_id`),
 KEY `FK_issues_issue_types` (`issue_type_id`),
 KEY `IX_issue_number` (`number`),
 KEY `price_guide_id_release` (`price_guide_id_release`),
 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`price_guide_id_release`) REFERENCES `price_guides` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=215505 DEFAULT CHARSET=utf8

Issue Prices:

CREATE TABLE `issue_prices` (
 `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
 `issue_id` bigint(11) unsigned NOT NULL,
 `price_guide_id` bigint(11) unsigned NOT NULL,
 `price` decimal(10,2) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uc_IssuePrices` (`price_guide_id`,`issue_id`),
 KEY `FK_issue_prices_issues` (`issue_id`),
 CONSTRAINT `FK_issue_prices_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`id`),
 CONSTRAINT `FK_issue_prices_price_guides` FOREIGN KEY (`price_guide_id`) REFERENCES `price_guides` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14125819 DEFAULT CHARSET=utf8

Price Guides:

CREATE TABLE `price_guides` (
 `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
 `effective_date` datetime NOT NULL,
 `released` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=utf8

Answer 1

1 > Using the curly brackets {} to format your response for better readability.

First split this query into three parts, replacing `OR` from `where` clause 

--- (issues.price_guide_id_release = users.price_guide_id `OR` issues.updated > DATE_SUB(NOW(), INTERVAL 1 MONTH) `OR` issue_prices.price_guide_id = users.price_guide_id), 
    and use UNION ALL with three constraints i.e.
    select ....
    from ....
    where .... and issues.price_guide_id_release = users.price_guide_id
    UNION ALL
    select ....
    from ....
    where .... and issues.updated > DATE_SUB(NOW(), INTERVAL 1 MONTH)
    UNION ALL
    select ....
    from ....
    where .... and issue_prices.price_guide_id = users.price_guide_id 

2> Try to avoid using NOT IN condition as it is the worst part of your query. You can add a flag column in issue_prices table for this purpose.

3> Index all those tables