Optimizing for connections with high latency

matsg posted 2 years ago in Creating a connection


Just an idea - I have connections to MySQL databases on the other side of the world and have quite high ping times to them. Creating a new connection, or opening an existing database, results in a number of SQL queries that looks up database/table structure and so on, and it takes a while before all of these complete.

Wouldn't it be possible to ask most of these queries in a single SQL statement? I'm counting 15 different queries (3 for connecting to the server, 12 for opening a database) that could just be all run in one go and fetched with multiple result sets. I think?

Regards Matt

ansgar posted 2 years ago

Well, that's excellent idea, from a performance point of view. In fact these internal queries are nested in different procedures which are spread in the HeidiSQL code, and partly depend on the result of each other. That makes it quite impossible to fire a bunch of queries in one go. The only bunch I could put in a single query is the queries for getting tables, triggers, procedures, views and events. These all live in one procedure, and I see a slight chance that I could get them with a multi statement query.

Please login to leave a reply, or register at first.