Same query, different execution time on MySQL 5.5 and 5.7. (MySQL 5.5 not using the index)
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`);
mysql sql query-optimization query-performance
add a comment |
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`);
mysql sql query-optimization query-performance
2
Database version updates often include better query optimization. (So, version downgrades can remove optimizations.) Your best bet for fixing this is to repeat your query-optimization work on the downgraded platform. What indexes do the tables have? Please edit your question to tell us.
– O. Jones
Nov 12 '18 at 11:51
@O.Jones Edited.
– ProGM
Nov 12 '18 at 11:58
This seems to be an example of where ICP ("Using index condition") shines. (But the real fix is to add better indexes; see O.Jones's Answer.)
– Rick James
Nov 12 '18 at 16:32
add a comment |
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`);
mysql sql query-optimization query-performance
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`);
mysql sql query-optimization query-performance
mysql sql query-optimization query-performance
edited Nov 12 '18 at 16:25
Rick James
66.1k55897
66.1k55897
asked Nov 12 '18 at 11:44
ProGM
4,64742342
4,64742342
2
Database version updates often include better query optimization. (So, version downgrades can remove optimizations.) Your best bet for fixing this is to repeat your query-optimization work on the downgraded platform. What indexes do the tables have? Please edit your question to tell us.
– O. Jones
Nov 12 '18 at 11:51
@O.Jones Edited.
– ProGM
Nov 12 '18 at 11:58
This seems to be an example of where ICP ("Using index condition") shines. (But the real fix is to add better indexes; see O.Jones's Answer.)
– Rick James
Nov 12 '18 at 16:32
add a comment |
2
Database version updates often include better query optimization. (So, version downgrades can remove optimizations.) Your best bet for fixing this is to repeat your query-optimization work on the downgraded platform. What indexes do the tables have? Please edit your question to tell us.
– O. Jones
Nov 12 '18 at 11:51
@O.Jones Edited.
– ProGM
Nov 12 '18 at 11:58
This seems to be an example of where ICP ("Using index condition") shines. (But the real fix is to add better indexes; see O.Jones's Answer.)
– Rick James
Nov 12 '18 at 16:32
2
2
Database version updates often include better query optimization. (So, version downgrades can remove optimizations.) Your best bet for fixing this is to repeat your query-optimization work on the downgraded platform. What indexes do the tables have? Please edit your question to tell us.
– O. Jones
Nov 12 '18 at 11:51
Database version updates often include better query optimization. (So, version downgrades can remove optimizations.) Your best bet for fixing this is to repeat your query-optimization work on the downgraded platform. What indexes do the tables have? Please edit your question to tell us.
– O. Jones
Nov 12 '18 at 11:51
@O.Jones Edited.
– ProGM
Nov 12 '18 at 11:58
@O.Jones Edited.
– ProGM
Nov 12 '18 at 11:58
This seems to be an example of where ICP ("Using index condition") shines. (But the real fix is to add better indexes; see O.Jones's Answer.)
– Rick James
Nov 12 '18 at 16:32
This seems to be an example of where ICP ("Using index condition") shines. (But the real fix is to add better indexes; see O.Jones's Answer.)
– Rick James
Nov 12 '18 at 16:32
add a comment |
2 Answers
2
active
oldest
votes
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.
Adding to the Pro tip: Put=
columns first.
– Rick James
Nov 12 '18 at 16:33
Good advice, wrong reasons. Ifdetection
is used first, the order(date, spot_id)
is better because it is 'covering'. Else,(spot_id, date)
is better so that the=
comes first.
– Rick James
Nov 12 '18 at 16:37
add a comment |
I would try moving the where clause predicates that don't filter the calendar table into join predicates, if nothing else it helps with readability but can also help the engine to compile a more optimal plan.
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`
AND `detection`.`date` BETWEEN '2017-11-17' AND '2017-11-23'
WHERE
`calendar`.`starts_at` = '2017-11-17'
AND `calendar`.`user_id` = 73
It's also possible that the index needs rebuilding following the downgrade, you can do this for each table with the below.
OPTIMIZE TABLE `calendar`;
OPTIMIZE TABLE `spot`;
OPTIMIZE TABLE `detection`;
This does lock the tables whilst it's running though, so bear that in mind on a production DB.
Finally, is spot
.product
a foreign key of calendar
.product
or vice versa? Are they exactly the same Data Type?
I've tried your query and also the "optimize" statement, but there's no substantial change. theproduct
field is a VARCHAR, containing the name of the product. (I haven't designed this database, and I have to keep it as-is since the client software depend on it) :(
– ProGM
Nov 12 '18 at 12:21
I'm pretty sure the Optimizer ignores whether you put things inON
versusWHERE
.
– Rick James
Nov 12 '18 at 16:30
OPTIMIZE TABLE
is rarely useful; don't bother.
– Rick James
Nov 12 '18 at 16:30
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53261465%2fsame-query-different-execution-time-on-mysql-5-5-and-5-7-mysql-5-5-not-using%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Adding to the Pro tip: Put=
columns first.
– Rick James
Nov 12 '18 at 16:33
Good advice, wrong reasons. Ifdetection
is used first, the order(date, spot_id)
is better because it is 'covering'. Else,(spot_id, date)
is better so that the=
comes first.
– Rick James
Nov 12 '18 at 16:37
add a comment |
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.
Adding to the Pro tip: Put=
columns first.
– Rick James
Nov 12 '18 at 16:33
Good advice, wrong reasons. Ifdetection
is used first, the order(date, spot_id)
is better because it is 'covering'. Else,(spot_id, date)
is better so that the=
comes first.
– Rick James
Nov 12 '18 at 16:37
add a comment |
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.
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.
answered Nov 12 '18 at 12:35
O. Jones
59.4k971106
59.4k971106
Adding to the Pro tip: Put=
columns first.
– Rick James
Nov 12 '18 at 16:33
Good advice, wrong reasons. Ifdetection
is used first, the order(date, spot_id)
is better because it is 'covering'. Else,(spot_id, date)
is better so that the=
comes first.
– Rick James
Nov 12 '18 at 16:37
add a comment |
Adding to the Pro tip: Put=
columns first.
– Rick James
Nov 12 '18 at 16:33
Good advice, wrong reasons. Ifdetection
is used first, the order(date, spot_id)
is better because it is 'covering'. Else,(spot_id, date)
is better so that the=
comes first.
– Rick James
Nov 12 '18 at 16:37
Adding to the Pro tip: Put
=
columns first.– Rick James
Nov 12 '18 at 16:33
Adding to the Pro tip: Put
=
columns first.– Rick James
Nov 12 '18 at 16:33
Good advice, wrong reasons. If
detection
is used first, the order (date, spot_id)
is better because it is 'covering'. Else, (spot_id, date)
is better so that the =
comes first.– Rick James
Nov 12 '18 at 16:37
Good advice, wrong reasons. If
detection
is used first, the order (date, spot_id)
is better because it is 'covering'. Else, (spot_id, date)
is better so that the =
comes first.– Rick James
Nov 12 '18 at 16:37
add a comment |
I would try moving the where clause predicates that don't filter the calendar table into join predicates, if nothing else it helps with readability but can also help the engine to compile a more optimal plan.
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`
AND `detection`.`date` BETWEEN '2017-11-17' AND '2017-11-23'
WHERE
`calendar`.`starts_at` = '2017-11-17'
AND `calendar`.`user_id` = 73
It's also possible that the index needs rebuilding following the downgrade, you can do this for each table with the below.
OPTIMIZE TABLE `calendar`;
OPTIMIZE TABLE `spot`;
OPTIMIZE TABLE `detection`;
This does lock the tables whilst it's running though, so bear that in mind on a production DB.
Finally, is spot
.product
a foreign key of calendar
.product
or vice versa? Are they exactly the same Data Type?
I've tried your query and also the "optimize" statement, but there's no substantial change. theproduct
field is a VARCHAR, containing the name of the product. (I haven't designed this database, and I have to keep it as-is since the client software depend on it) :(
– ProGM
Nov 12 '18 at 12:21
I'm pretty sure the Optimizer ignores whether you put things inON
versusWHERE
.
– Rick James
Nov 12 '18 at 16:30
OPTIMIZE TABLE
is rarely useful; don't bother.
– Rick James
Nov 12 '18 at 16:30
add a comment |
I would try moving the where clause predicates that don't filter the calendar table into join predicates, if nothing else it helps with readability but can also help the engine to compile a more optimal plan.
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`
AND `detection`.`date` BETWEEN '2017-11-17' AND '2017-11-23'
WHERE
`calendar`.`starts_at` = '2017-11-17'
AND `calendar`.`user_id` = 73
It's also possible that the index needs rebuilding following the downgrade, you can do this for each table with the below.
OPTIMIZE TABLE `calendar`;
OPTIMIZE TABLE `spot`;
OPTIMIZE TABLE `detection`;
This does lock the tables whilst it's running though, so bear that in mind on a production DB.
Finally, is spot
.product
a foreign key of calendar
.product
or vice versa? Are they exactly the same Data Type?
I've tried your query and also the "optimize" statement, but there's no substantial change. theproduct
field is a VARCHAR, containing the name of the product. (I haven't designed this database, and I have to keep it as-is since the client software depend on it) :(
– ProGM
Nov 12 '18 at 12:21
I'm pretty sure the Optimizer ignores whether you put things inON
versusWHERE
.
– Rick James
Nov 12 '18 at 16:30
OPTIMIZE TABLE
is rarely useful; don't bother.
– Rick James
Nov 12 '18 at 16:30
add a comment |
I would try moving the where clause predicates that don't filter the calendar table into join predicates, if nothing else it helps with readability but can also help the engine to compile a more optimal plan.
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`
AND `detection`.`date` BETWEEN '2017-11-17' AND '2017-11-23'
WHERE
`calendar`.`starts_at` = '2017-11-17'
AND `calendar`.`user_id` = 73
It's also possible that the index needs rebuilding following the downgrade, you can do this for each table with the below.
OPTIMIZE TABLE `calendar`;
OPTIMIZE TABLE `spot`;
OPTIMIZE TABLE `detection`;
This does lock the tables whilst it's running though, so bear that in mind on a production DB.
Finally, is spot
.product
a foreign key of calendar
.product
or vice versa? Are they exactly the same Data Type?
I would try moving the where clause predicates that don't filter the calendar table into join predicates, if nothing else it helps with readability but can also help the engine to compile a more optimal plan.
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`
AND `detection`.`date` BETWEEN '2017-11-17' AND '2017-11-23'
WHERE
`calendar`.`starts_at` = '2017-11-17'
AND `calendar`.`user_id` = 73
It's also possible that the index needs rebuilding following the downgrade, you can do this for each table with the below.
OPTIMIZE TABLE `calendar`;
OPTIMIZE TABLE `spot`;
OPTIMIZE TABLE `detection`;
This does lock the tables whilst it's running though, so bear that in mind on a production DB.
Finally, is spot
.product
a foreign key of calendar
.product
or vice versa? Are they exactly the same Data Type?
answered Nov 12 '18 at 12:04
Dazz Knowles
38119
38119
I've tried your query and also the "optimize" statement, but there's no substantial change. theproduct
field is a VARCHAR, containing the name of the product. (I haven't designed this database, and I have to keep it as-is since the client software depend on it) :(
– ProGM
Nov 12 '18 at 12:21
I'm pretty sure the Optimizer ignores whether you put things inON
versusWHERE
.
– Rick James
Nov 12 '18 at 16:30
OPTIMIZE TABLE
is rarely useful; don't bother.
– Rick James
Nov 12 '18 at 16:30
add a comment |
I've tried your query and also the "optimize" statement, but there's no substantial change. theproduct
field is a VARCHAR, containing the name of the product. (I haven't designed this database, and I have to keep it as-is since the client software depend on it) :(
– ProGM
Nov 12 '18 at 12:21
I'm pretty sure the Optimizer ignores whether you put things inON
versusWHERE
.
– Rick James
Nov 12 '18 at 16:30
OPTIMIZE TABLE
is rarely useful; don't bother.
– Rick James
Nov 12 '18 at 16:30
I've tried your query and also the "optimize" statement, but there's no substantial change. the
product
field is a VARCHAR, containing the name of the product. (I haven't designed this database, and I have to keep it as-is since the client software depend on it) :(– ProGM
Nov 12 '18 at 12:21
I've tried your query and also the "optimize" statement, but there's no substantial change. the
product
field is a VARCHAR, containing the name of the product. (I haven't designed this database, and I have to keep it as-is since the client software depend on it) :(– ProGM
Nov 12 '18 at 12:21
I'm pretty sure the Optimizer ignores whether you put things in
ON
versus WHERE
.– Rick James
Nov 12 '18 at 16:30
I'm pretty sure the Optimizer ignores whether you put things in
ON
versus WHERE
.– Rick James
Nov 12 '18 at 16:30
OPTIMIZE TABLE
is rarely useful; don't bother.– Rick James
Nov 12 '18 at 16:30
OPTIMIZE TABLE
is rarely useful; don't bother.– Rick James
Nov 12 '18 at 16:30
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53261465%2fsame-query-different-execution-time-on-mysql-5-5-and-5-7-mysql-5-5-not-using%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Database version updates often include better query optimization. (So, version downgrades can remove optimizations.) Your best bet for fixing this is to repeat your query-optimization work on the downgraded platform. What indexes do the tables have? Please edit your question to tell us.
– O. Jones
Nov 12 '18 at 11:51
@O.Jones Edited.
– ProGM
Nov 12 '18 at 11:58
This seems to be an example of where ICP ("Using index condition") shines. (But the real fix is to add better indexes; see O.Jones's Answer.)
– Rick James
Nov 12 '18 at 16:32