user784637
user784637

Reputation: 16152

Why are queries executed from mysql workbench taking much longer than executing them directly from the mysql cli?

I have a query that does multiple joins on several tables and uses a covering index. It seems to run a lot faster when executed directly from the mysql cli instead of from mysql workbench.

mysql > some query
...
250 rows in set (0.05 sec)

from mysql workbench it takes about ~0.200 sec duration / 0.100 sec fetch

Is there a reason beyond latency, authentication and transfer of data why it would be orders of magnitude faster on the console? Does a three-way tcp handshake occur each time a query is executed from mysql workbench or does that tcp connection stay open until you close the workbench session?

Upvotes: 3

Views: 4257

Answers (1)

Mike Lischke
Mike Lischke

Reputation: 53502

There's a persistent connection in Workbench, so there's no overhead in that regard. Executing a query should be equally fast in both CLI as well as Workbench, however data transfer is a bit slower sometimes because Workbench locally caches results first to be able to sort over it when the user clicks a header field in the resultset view (including multi column sort). Try repeating the query in both tools and see if the times still differ. The first run of a query is usually slower than following runs due to the execution cache.

Upvotes: 2

Related Questions