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
IN ( SELECT ... )
into aJOIN
. Then, provideSHOW CREATE TABLE
for the tables. Then I will think about digging into it.FROM ( SELECT ... ) JOIN issue_prices ...