Bug #100812 slow query unless connected using WorkBench
Submitted: 11 Sep 10:51 Modified: 29 Sep 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:Microsoft Windows
Assigned to: CPU Architecture:x86
Tags: performance

[11 Sep 10:51] Dani Kaplan
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
[23 Sep 12:00] MySQL Verification Team
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.
[23 Sep 12:22] Dani Kaplan
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 .
[23 Sep 12:24] Dani Kaplan
one more comment - the same table with the same query - on mariadb - shows the same execution time no matter which clients runs it.
[23 Sep 12:24] MySQL Verification Team
Hi,

Then, please, turn the query log as instructed and see what the final query looked like.
[28 Sep 12:21] MySQL Verification Team
Thank you for the feedback, Mr. Kaplan.

Not a bug.
[29 Sep 12:27] MySQL Verification Team
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.
[29 Sep 14:38] Dani Kaplan
since the data file is proprietary (and not mine)  - is there a way I can send it without having the public access to it ?
[29 Sep 15:14] MySQL Verification Team
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.