MySQL Bugs: #100812: slow query unless connected using WorkBench

Bug #100812	slow query unless connected using WorkBench
Submitted:	11 Sep 2020 10:51	Modified:	29 Sep 2020 14:38
Reporter:	Dani Kaplan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S5 (Performance)
Version:	8.0.21	OS:	Windows
Assigned to:		CPU Architecture:	x86
Tags:	performance

Description:
Running a query against mysql 8.0.21 on windows takes 7s when using the dot net connector or 3rd party clients. same query will take 0.9s when run from WorkBench 8.0

This is the table:
CREATE TABLE `e` (
   `cola` decimal(39,0) unsigned DEFAULT NULL,
   `colb` decimal(39,0) unsigned DEFAULT NULL,
   `a` char(2) COLLATE utf8_bin DEFAULT NULL,
   `b` varchar(64) COLLATE utf8_bin DEFAULT NULL,
   `c` varchar(128) COLLATE utf8_bin DEFAULT NULL,
   `d` varchar(128) COLLATE utf8_bin DEFAULT NULL,
   KEY `idx_cola_colb` (`cola`,`colb`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

This is the query: 
ELECT a, b, c FROM e WHERE cola<= 281471982716236 and 281471982716236 <= colb;

this is explain results: 
'1', 'SIMPLE', 'e', NULL, 'ALL', 'idx_cola_colb', NULL, NULL, NULL, '10466472', '16.66', 'Using where'

(mainly no key was used) 

I have tested this on both windows 2016 server and windows 10 

Calling from workbench 0.9s
Calling from Navicat or through mysql.data connector 7s

How to repeat:
Install 8.0.21 server on windows system. 
Populate the table with 10M rows
(each row has a range cola-->colb) 

run the query in workbench, then run it in code / navicat.

row sample:

58568984968339333397457352665040158720	58568985047567495911721690258584109055	TE TB	TG TH

Hi Mr. Kaplan,

Thank you for your bug report.

It is quite possible that Workbench rewrites the query in order to get index utilised. It is also possible that some optimiser switches are added.

You can first try to change the last expression to :

AND  colb >= 281471982716236;

If that does not help, turn the query log on and see what did the final query look like.

changing the order of the parameters didn't change a thing
7s on navicat, same on workbench: 1.25s 

I've run it with performance schema - the results can be seen here:

https://stackoverflow.com/questions/63807405/remote-query-against-mysql-server-takes-much-...

slow log shows different execution time to the same query - depends where it came from .

one more comment - the same table with the same query - on mariadb - shows the same execution time no matter which clients runs it.

Hi,

Then, please, turn the query log as instructed and see what the final query looked like.

Thank you for the feedback, Mr. Kaplan.

Not a bug.

Hi Mr. Kaplan,

We are ready to examine your case, but we require data for that table. Sufficient number of data so that problem might be shown.

You can send us the tablespace with .cfg file, instead. Yo ucan compress it and upload it using "Files" tab.

since the data file is proprietary (and not mine)  - is there a way I can send it without having the public access to it ?

Yes,

Use the "Files" tab.

It is accessible only to the few of us.

Do not forget both the tablespace and .cfg file, compressed together.