Slow query on smaller MySQL server

Question

I am running MySQL 5.7 on both my server and my local machine. I am using Symfony 4.4 and Doctrine. On my dev machine the following query (with the same DB dumped from the server) executes in ~2s, while it takes 35s+ on the server. I assume this is linked to limitations of the server (less RAM, etc.) but I can't really throw additional memory in there. Therefore, I am looking at how I could improve the following the query - originally generated by Doctrine. I replicated the same slowness by executing the same query directly in phpMyAdmin on the server so I know for sure the query is responsible. I am a bit stuck here and would appreciate any help or pointers in the right direction: Do I need to try to split the queries? Should I try to add indexes (besides the PK and FK the column referenced in the where clause are not indexed) ? Thank you all for the help!

SELECT DISTINCT id_0 FROM (
    SELECT DISTINCT id_0, pivot_price_5 FROM (
        SELECT b0_.id AS id_0, b0_.price_drop AS price_drop_1, o1_.id AS id_2, o1_.price AS price_3, o1_.currency AS currency_4, o1_.pivot_price AS pivot_price_5, o1_.price_drop AS price_drop_6, o1_.date AS date_7, p2_.id AS id_8, p2_.name AS name_9, p2_.description AS description_10, p2_.normal_price AS normal_price_11, p2_.link AS link_12, p2_.image_link AS image_link_13, p2_.image_thumb_link AS image_thumb_link_14, p2_.merchant_product_id AS merchant_product_id_15, p2_.slug AS slug_16, p2_.created_at AS created_at_17, p2_.updated_at AS updated_at_18, p3_.id AS id_19, p3_.ean AS ean_20, p3_.last_game_check_date AS last_game_check_date_21, p3_.created_at AS created_at_22, p3_.updated_at AS updated_at_23, g4_.id AS id_24, g4_.game_system_key AS game_system_key_25, g4_.created_at AS created_at_26, g4_.updated_at AS updated_at_27 
        FROM best_offer b0_ 
        INNER JOIN offer o1_ ON b0_.offer_id = o1_.id 
        INNER JOIN product_version p2_ ON o1_.product_version_id = p2_.id 
        INNER JOIN product p3_ ON b0_.product_id = p3_.id 
        INNER JOIN product_game_system p5_ ON p3_.id = p5_.product_id 
        INNER JOIN game_system g4_ ON g4_.id = p5_.game_system_id 
        WHERE (o1_.date >= '2020-07-29 00:00:00' AND o1_.date <= '2020-07-29 23:59:59') 
        AND o1_.pivot_price >= '0' 
        AND o1_.pivot_price <= '2208' 
        AND g4_.game_system_key IN ('NSW', 'PS4', 'ONE')
    ) dctrn_result_inner 
    ORDER BY pivot_price_5 ASC
) dctrn_result LIMIT 8 OFFSET 40

For completion sake, the PHP code is:

// In Repository
$qb = $this->createQueryBuilder('best_offer')
            ->join('best_offer.offer', 'offer')
            ->addSelect('offer')
            ->join('offer.productVersion', 'productVersion')
            ->addSelect('productVersion')
            ->join('best_offer.product', 'product')
            ->addSelect('product')
            ->join('product.gameSystems', 'gameSystems')
            ->addSelect('gameSystems')
        ;

        
            $qb
                ->join('product.game', 'game')
                ->join('game.ratings', 'game_ratings')
                ->andWhere('game_ratings.type = :gameRatingType')
                ->setParameter('gameRatingType', GameRating::TYPE_METACRITIC)
                ->andWhere('game_ratings.rating > :gameRatingValue')
                ->setParameter('gameRatingValue', $minMetacritic)
            ;
        

            $qb = $qb->addCriteria(OfferRepository::createCriteriaOnDate($datetime, 'offer'));

            $qb->andWhere('offer.pivotPrice >= :minPivotPrice')
                ->setParameter('minPivotPrice', $minPivotPrice*100)
            ;

            $qb = $qb->addCriteria(OfferRepository::createCriteriaMaxPivotPrice($maxPivotPrice, 'offer'));

            $qb = $qb->addCriteria(GameSystemRepository::createCriteriaSystemsIn($gameSystems, 'gameSystems'));

            $qb = $qb->setMaxResults($limit);

            foreach ($sortBy as $sortKey => $sortValue) {
                $qb = $qb->orderBy($sortKey, $sortValue);
            }
       return $qb;

called by the PagerFanta in the Controller:

// In Controller
$adapter = new DoctrineORMAdapter($qb);
$pagerFanta = new Pagerfanta($adapter);
$pagerFanta->setMaxPerPage(8);
$pagerFanta->setCurrentPage($page);

Explain results:

+----+-------------+------------+------------+--------+--------------------------------------------------+-----------------------+---------+--------------------------------+------+----------+-----------------------------------------------------------+--+
| id | select_type |   table    | partitions |  type  |                  possible_keys                   |          key          | key_len |              ref               | rows | filtered |                           Extra                           |  |
+----+-------------+------------+------------+--------+--------------------------------------------------+-----------------------+---------+--------------------------------+------+----------+-----------------------------------------------------------+--+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                                             | NULL                  | NULL    | NULL                           | 2268 | 100.00   | Using temporary                                           |  |
|  2 | DERIVED     | g4_        | NULL       | range  | PRIMARY,UNIQ_B478BC43A9F4C69F                    | UNIQ_B478BC43A9F4C69F | 14      | NULL                           |    3 | 100.00   | Using where; Using index; Using temporary; Using filesort |  |
|  2 | DERIVED     | p5_        | NULL       | ref    | PRIMARY,IDX_1857225C4584665A,IDX_1857225C233EEA7 | IDX_1857225C233EEA7   | 4       | vgdeals.g4_.id                 |  377 | 100.00   | Using index                                               |  |
|  2 | DERIVED     | p3_        | NULL       | eq_ref | PRIMARY                                          | PRIMARY               | 4       | vgdeals.p5_.product_id         |    1 | 100.00   | Using index                                               |  |
|  2 | DERIVED     | b0_        | NULL       | ref    | UNIQ_8B8D09A53C674EE,IDX_8B8D09A4584665A         | IDX_8B8D09A4584665A   | 4       | vgdeals.p5_.product_id         |   40 | 100.00   | NULL                                                      |  |
|  2 | DERIVED     | o1_        | NULL       | eq_ref | PRIMARY,IDX_29D6873ED8DB782E                     | PRIMARY               | 4       | vgdeals.b0_.offer_id           |    1 | 5.00     | Using where                                               |  |
|  2 | DERIVED     | p2_        | NULL       | eq_ref | PRIMARY                                          | PRIMARY               | 4       | vgdeals.o1_.product_version_id |    1 | 100.00   | Using index                                               |  |
+----+-------------+------------+------------+--------+--------------------------------------------------+-----------------------+---------+--------------------------------+------+----------+-----------------------------------------------------------+--+

The SHOW CREATE TABLE for the involved tables is below (sorry I couldn't find a way to format this properly in SO):

BEST_OFFER

CREATE TABLE `best_offer` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `product_id` int(11) NOT NULL,
     `offer_id` int(11) NOT NULL,
     `price_drop` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `UNIQ_8B8D09A53C674EE` (`offer_id`),
     KEY `IDX_8B8D09A4584665A` (`product_id`),
     CONSTRAINT `FK_8B8D09A4584665A` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`),
     CONSTRAINT `FK_8B8D09A53C674EE` FOREIGN KEY (`offer_id`) REFERENCES `offer` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=317260 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

OFFER

CREATE TABLE `offer` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `product_version_id` int(11) NOT NULL,
 `price` int(10) unsigned NOT NULL,
 `currency` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
 `pivot_price` int(11) NOT NULL,
 `price_drop` int(11) DEFAULT NULL,
 `date` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `IDX_29D6873ED8DB782E` (`product_version_id`),
 CONSTRAINT `FK_29D6873ED8DB782E` FOREIGN KEY (`product_version_id`) REFERENCES `product_version` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=497233 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

PRODUCT_VERSION

CREATE TABLE `product_version` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `merchant_id` int(11) NOT NULL,
 `product_id` int(11) NOT NULL,
 `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `description` longtext COLLATE utf8mb4_unicode_ci,
 `normal_price` int(10) unsigned DEFAULT NULL,
 `link` varchar(4000) COLLATE utf8mb4_unicode_ci NOT NULL,
 `image_link` varchar(4000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `image_thumb_link` varchar(4000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `merchant_product_id` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
 `slug` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `UNIQ_6EC5C873989D9B62` (`slug`),
 KEY `IDX_6EC5C8736796D554` (`merchant_id`),
 KEY `IDX_6EC5C8734584665A` (`product_id`),
 CONSTRAINT `FK_6EC5C8734584665A` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`),
 CONSTRAINT `FK_6EC5C8736796D554` FOREIGN KEY (`merchant_id`) REFERENCES `merchant` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10775 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

PRODUCT

CREATE TABLE `product` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `ean` char(13) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `game_id` int(11) DEFAULT NULL,
 `last_game_check_date` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `UNIQ_D34A04AD67B1C660` (`ean`),
 KEY `IDX_D34A04ADE48FD905` (`game_id`),
 CONSTRAINT `FK_D34A04ADE48FD905` FOREIGN KEY (`game_id`) REFERENCES `game` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6450 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

PRODUCT_GAME_SYSTEM

CREATE TABLE `product_game_system` (
 `product_id` int(11) NOT NULL,
 `game_system_id` int(11) NOT NULL,
 PRIMARY KEY (`product_id`,`game_system_id`),
 KEY `IDX_1857225C4584665A` (`product_id`),
 KEY `IDX_1857225C233EEA7` (`game_system_id`),
 CONSTRAINT `FK_1857225C233EEA7` FOREIGN KEY (`game_system_id`) REFERENCES `game_system` (`id`) ON DELETE CASCADE,
 CONSTRAINT `FK_1857225C4584665A` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

GAME_SYSTEM

CREATE TABLE `game_system` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `game_system_key` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `UNIQ_B478BC43A9F4C69F` (`game_system_key`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Finally, here is the SHOW TABLE STATUS

+---------------------+--------+----+---------+--------+------+----------+---+---------+---------+--------+---------------------+---------------------+---------------------+---------------------+--+--------------------+--+--------------------+--+----------+--+--+--+--+
|     best_offer      | InnoDB | 10 | Dynamic | 307651 |  46  | 14172160 | 0 | 9469952 | 5242880 | 317260 | 2020-07-29 22:16:33 | 2020-07-31 07:59:09 |                     |          NULL       |  | utf8mb4_unicode_ci |  |          NULL      |  |          |  |  |  |  |
+---------------------+--------+----+---------+--------+------+----------+---+---------+---------+--------+---------------------+---------------------+---------------------+---------------------+--+--------------------+--+--------------------+--+----------+--+--+--+--+
| game_system         | InnoDB | 10 | Dynamic |     17 |  963 |    16384 | 0 |   16384 |       0 |     18 | 2020-07-29 22:16:36 |                     |     NULL            |                     |  |     NULL           |  | utf8mb4_unicode_ci |  |     NULL |  |  |  |  |
| offer               | InnoDB | 10 | Dynamic | 460330 |   60 | 27836416 | 0 | 7880704 | 6291456 | 497233 | 2020-07-29 22:16:44 | 2020-07-31 07:59:09 |                     |     NULL            |  | utf8mb4_unicode_ci |  |     NULL           |  |          |  |  |  |  |
| product             | InnoDB | 10 | Dynamic |   6432 |   63 |   409600 | 0 |  294912 |       0 |   6450 | 2020-07-29 22:16:44 | 2020-07-31 08:00:57 |                     |     NULL            |  | utf8mb4_unicode_ci |  |     NULL           |  |          |  |  |  |  |
| product_game_system | InnoDB | 10 | Dynamic |   6419 |   33 |   212992 | 0 |  229376 |       0 |        |     NULL            |                     | 2020-07-29 22:16:44 | 2020-07-31 07:57:15 |  |     NULL           |  | utf8mb4_unicode_ci |  |     NULL |  |  |  |  |
| product_version     | InnoDB | 10 | Dynamic |  10749 | 2297 | 24690688 | 0 | 1916928 | 7340032 |  10775 | 2020-07-29 22:16:50 | 2020-07-31 07:59:00 |                     |     NULL            |  | utf8mb4_unicode_ci |  |     NULL           |  |          |  |  |  |  |
+---------------------+--------+----+---------+--------+------+----------+---+---------+---------+--------+---------------------+---------------------+---------------------+---------------------+--+--------------------+--+--------------------+--+----------+--+--+--+--+

You can run MySQL query with EXPLAIN statement to see what is going on. Or just remove joins and condition clauses and add them one by one, to see which one slows down query execution. Then add indexes to columns used in slow join (clause). You can also consider caching — empy26, Jul 30 '20 at 19:45
In addition to the `EXPLAINs`, please provide `SHOW CREATE TABLE`. — Rick James, Jul 30 '20 at 20:05
I added the explain output of the query to the main question. — Schyzophrenic, Jul 30 '20 at 20:25

score 1 · Accepted Answer · answered Jul 30 '20 at 20:19

1

The ORDER BY pivot_price_5 ASC is useless. This is because a subquery is, but definition, an unordered set. (Adding a LIMIT makes it no useless.) But it seems like you should get rid of the inner subquery.

DISTINCT with LIMIT -- you are aware that the DISTINCT happens first?

There are two ranges and one IN in the main WHERE; only one of them can use an index. I suggest you have each of thefollowing so that the Optimizer can pick the better. (Note: With a different dataset, the Optimizer may pick a different INDEX, with different performance.)

INDEX(pivot_price)
INDEX(date)

Please provide EXPLAINs, CREATE TABLEs, and SHOW TABLE STATUS. (I want to analyze whether using partitioning for your "2-dimensional" WHERE would be worth pursuing.)

It looks like you are fetching several columns from many of the tables, only to eventually ignore those extra columns. Cleaning that up will help performance.

answered Jul 30 '20 at 20:19

Rick James

135,179
13
127
222

Thank you for helping! Because this query is generated via the Doctrine ORM I have limited leeway for changing the order of things. I added the EXPLAIN, SHOW TABLE hoping we can shed some light on this – Schyzophrenic Jul 31 '20 at 09:22
I added both indexes. I started with an index on pivot_price, no difference. Then I added the index on DATE and I moved from a query that took 35/38s to 0.7s! Thank you guys for the help! – Schyzophrenic Jul 31 '20 at 11:25
@Schyzophrenic - Good! That says that 0..2208 spans a lot of the table, while the 1 day (for `date`) is only a small fraction of the table. Shrinking the price range or increasing the date range may cause the Optimizer to switch the index to use. And that should show up in `EXPLAIN`. – Rick James Jul 31 '20 at 16:22
1

@Schyzophrenic - If _both_ ranges are "large", the Optmizer will punt, and the query will be back to 35/38s. Encourage users to pick tight ranges. – Rick James Jul 31 '20 at 16:24
these are definitely good advices, and I will monitor this query in the future. The size of the table grows by ~8,000/day but the average number of rows per day remains pretty stable. It is going to be interesting to monitor these stats and link them to the performances of the query – Schyzophrenic Jul 31 '20 at 20:38

Slow query on smaller MySQL server

1 Answers1