MySQL Forums
Forum List  »  MySQL Workbench

v8.0.16 - using variables prevents correct use of available indexes
Posted by: Craig Watkinson
Date: June 12, 2019 05:48AM

I have a complex query that executed in <0.1s in v8.0.15, but takes >300s in v8.0.16! This is due to indexes not being used when I substitute values into a query using variables. Has this behaviour changed, or is it possibly a bug?

The following queries demonstrate the issue:

# Query 1 - performs a range scan on s3Filename index => 750 rows
SELECT count(*) FROM logfiles
WHERE s3Filename > "20190308T104730.760958700Z.log"
AND s3Filename <= "20190308T112911.460695287Z.log";

# Query 2 - performs a full index scan on s3Filename index => 7M rows
set @start_s3Filename = "20190308T104730.760958700Z.log";
set @end_s3Filename = "20190308T112911.460695287Z.log";
SELECT count(*) FROM logfiles
WHERE s3Filename > @start_s3Filename
AND s3Filename <= @end_s3Filename;

I would expect these to perform identically, and for the EXPLAIN to be the same, but on v8.0.16 it is not. On v8.0.15, both queries perform a range scan. Adding a "USE INDEX" clause did not help.

I'm also surprised that the client is able to affect the way that a query is executed. I presumed the execution plan would be determined server side. Is this done client side? If yes, then I presume this means that I could get different behaviour with different clients e.g. MySQL Workbench vs. SQLAlchemy, which is not good news.

Thanks Craig

Options: ReplyQuote


Subject
Views
Written By
Posted
v8.0.16 - using variables prevents correct use of available indexes
99
June 12, 2019 05:48AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.