Same query, different execution time on MySQL 5.5 and 5.7. (MySQL 5.5 not using the index)

Question

For compatibility reasons, I had to downgrade a production database from MySQL 5.7 to MySQL 5.5.

After moving to 5.5, I've noticed that this query has become MUCH slower, from like 200ms to like ~20 seconds of execution.

Here's the query:

SELECT
  COUNT(*)
FROM
  `calendar`
INNER JOIN
  `spot` ON `spot`.`product` = `calendar`.`product`
        AND `spot`.`company_id` = `calendar`.`company_id`
INNER JOIN
  `detection` ON `detection`.`spot_id` = `spot`.`id`
WHERE `calendar`.`starts_at` = '2017-11-17'
  AND `calendar`.`user_id` = 73
  AND `detection`.`date` >= '2017-11-17'
  AND `detection`.`date` <= '2017-11-23'

Here's the EXPLAIN output for MySQL 5.5:

1 SIMPLE | calendar | ref starts_at_ends_at_index starts_at_ends_at_index 3 const 1204 | Using where
1 SIMPLE | spot ref PRIMARY,company_id_index,product_index | product_index | 302 calendar.product | 13 | Using where
1 SIMPLE | detection | ref spot_id_index,date_index | spot_id_index 48 | spot.Id | 80 | Using where

Here's the EXPLAIN output for MySQL 5.7:

1 SIMPLE | calendar | ref starts_at_ends_at_index starts_at_ends_at_index 3 const 1204 | Using where
1 SIMPLE | spot ref PRIMARY,company_id_index,product_index | product_index | 302 calendar.product | 13 | Using index condition; Using where
1 SIMPLE | detection | ref spot_id_index,date_index | spot_id_index 48 | spot.Id | 80 | Using where

The only difference I can see is that MySQL 5.7 uses: Using index condition; Using where on product_index, 5.5 don't.

I've tried to force the index usage, by specifying USE INDEX(product_index), but nothing changed

Any suggestion?

EDIT:

Current useful indexes:

ALTER TABLE `calendar` ADD INDEX `starts_at_ends_at_index` (`starts_at`, `ends_at`);

ALTER TABLE `spot` ADD INDEX `company_id_index` (`company_id`);

ALTER TABLE `spot` ADD INDEX `product_index` (`product`);

ALTER TABLE `detection` ADD INDEX `spot_id_index` (`spot_id`);

ALTER TABLE `detection` ADD INDEX `date_index` (`date`);

Answer 1

Your query filters calendar by two equality criteria, so those should appear in the same index as each other. It then uses the product column to access another table. So, put those three columns into one compound index. Try this:

 ALTER TABLE calendar ADD INDEX user_id_starts_at_product (user_id, starts_at, product);

Your query does a data range filter on detection, and also selects rows with particular values of spot_id. So try this compound index.

 ALTER TABLE detection ADD INDEX spot_id_date (spot_id, date);

Also try the compound index with the columns in the reverse order, and keep the index that gives you better performance.

 ALTER TABLE detection ADD INDEX date_spot_id (date, spot_id);

Try a compound index on spot to cover both filtering criteria (appearing on your ON clause).

  ALTER TABLE spot ADD INDEX company_id_product (company_id, product);

Pro tip: MySQL ordinarily can use only one index for each table per query (or subquery). So adding lots of single-column indexes is not generally a good way to make particular queries faster. Instead, adding compound indexes matching the requirements of your query is the way to go. This is true for various database versions.