0

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
  • Step 1: Turn IN ( SELECT ... ) into a JOIN. Then, provide SHOW CREATE TABLE for the tables. Then I will think about digging into it. – Rick James Jul 31 '16 at 3:05
  • @RickJames It's not a join because I was originally unable to do it that way but my attempt is above. I've provided the relevant show create tables. Thank you and please let me know if there's anything else that would help. – Matthew Snyder Jul 31 '16 at 21:10
  • FROM ( SELECT ... ) JOIN issue_prices ... – Rick James Jul 31 '16 at 22:48
0

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

  • 2, I don't believe the flag will work as I need that info on a per user and per issue basis so I don't believe that'd actually speed it up unfortunately... thanks for the suggestion though. 3. All of them are indexed and have been optimized – Matthew Snyder Aug 4 '16 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.