Bug #91566 Reading table information uses an inefficient series of commands

• Submitted: 6 Jul 2018 21:03
• Modified: 8 Jul 2018 11:58
• Reporter: Brendan Byrd
• Status: Verified
• Category: MySQL Server: Command-line Clients
• Severity: S4 (Feature request)
• Version: 5.6.37-82.2
• OS: Linux
• CPU Architecture: x86

[6 Jul 2018 21:03] Brendan Byrd

Description:
The table information acquiring feature is horribly inefficient.  For each table, it sends a Show Fields command.  Then it waits for the response, with a payload of only 150-250 bytes, and repeats the process.

This process is far too slow, especially for databases with high latency.  Instead of fixing the root cause, the warning and -A switch was implemented.

How to repeat:
1. Find a large database with lots of tables
2. Run mysql without the -A switch.

Suggested fix:
Send a command or query that requests the fields for ALL tables, all at once.  Let TCP split up the data into bigger chunks.

[8 Jul 2018 11:58] MySQL Verification Team

I do agree it could be improved, so this is a valid feature request.

FYI I logged the queries issued by 8.0 client, found this:

2018-07-08T11:55:44.430373Z	   15 Connect	root@192.168.1.105 on test using SSL/TLS
2018-07-08T11:55:44.437374Z	   15 Query	show databases
2018-07-08T11:55:44.451376Z	   15 Query	show tables
2018-07-08T11:55:44.470879Z	   15 Field List	t0000 
2018-07-08T11:55:44.474379Z	   15 Field List	t0001 
2018-07-08T11:55:44.477379Z	   15 Field List	t0002 
2018-07-08T11:55:44.479380Z	   15 Field List	t0003 
2018-07-08T11:55:44.484380Z	   15 Field List	t0004 
2018-07-08T11:55:44.487881Z	   15 Field List	t0005 
2018-07-08T11:55:44.491381Z	   15 Field List	t0006 
2018-07-08T11:55:44.494382Z	   15 Field List	t0007 
2018-07-08T11:55:44.497882Z	   15 Field List	t0008 
2018-07-08T11:55:44.501382Z	   15 Field List	t0009 
2018-07-08T11:55:44.504383Z	   15 Field List	v0000 
2018-07-08T11:55:44.507883Z	   15 Field List	v0001 
2018-07-08T11:55:44.517384Z	   15 Field List	v0002 
2018-07-08T11:55:44.521885Z	   15 Field List	v0003 
2018-07-08T11:55:44.526386Z	   15 Field List	v0004 
2018-07-08T11:55:44.530386Z	   15 Field List	v0005 
2018-07-08T11:55:44.534887Z	   15 Field List	v0006 
2018-07-08T11:55:44.540387Z	   15 Field List	v0007 
2018-07-08T11:55:44.543888Z	   15 Field List	v0008 
2018-07-08T11:55:44.547388Z	   15 Field List	v0009 

Of course on a very slow connection this round trip on each will take far longer than needed.