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 | | | | | | |
+---------------------+--------+----+---------+--------+------+----------+---+---------+---------+--------+---------------------+---------------------+---------------------+---------------------+--+--------------------+--+--------------------+--+----------+--+--+--+--+
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.