Cause of delay after use `$database`

Question

For a certain project, it seems like the command use <database>; takes an extraordinarily long time (often a minute or more). (This seems to be the case across a couple of different instances on different versions of MySQL (5.3 and 5.5, I think)). I understand that this is caused by the Tab complete functionality, and I am aware that I could just use a -A flag, but I am interested in the cause of this delay.

My first guess was that this has to do with the speed of INFORMATION_SCHEMA, and that the sheer number of columns caused a delay, but at 4.7k columns (580 tables at ~8 columns a table), that seems a bit of a stretch. There are 3.6k indexes, but that does not strike me as a likely cause of the issue.

Answer 1

You've pretty much hit the nail on the head.

If the use command results in a change of default database, then the MySQL client branches into build_completion_hash().

Provided that the client was not started with the -A option, that function:

1. hashes all SQL commands;

2. executes show databases and hashes the results;

3. executes show tables and hashes the results; and

4. for each table, calls mysql_list_fields() and hashes the results.

So, in your case, it is making 582 separate database requests (one for each of steps 2 and 3; and 580 for step 4), looping over the results of each one. Ouch.

Of course, it'd have been imminently more sensible to do (or at least first attempt to do) a single SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() but ours is not to ponder why…

Answer 2

There's a few possibilities I can think of:

• Loading the auto-complete data takes a really long time because you've got a schema that a very large number of tables and columns. Transmitting this isn't always very efficient, so as you note, -A might help. Is it slow connecting to the main mysql database?

• Otherwise maybe MySQL was at rest and needs a moment to spin up and get ready for your requests, though this should mean a second connection is much faster. Once the caches are warmed up, which includes the OS-level disk cache, it should perform better. On a lightly-loaded system this will often be the case.