c++ Mysql C API Connection Question

Question

I'm building an application which uses Mysql, I was wondering what would be the best way to manage the connection to the actual Mysql server?

I'm still in the design phase, but currently I have it Connecting (or aborting if error) before every query and disconnecting after which is just for testing as right now I'm only running 1 query to see if the code I've setup so far works.

My App might be performing a few queries every 5/10/20/30 minutes depending on settings and doesn't really need to do anything with SQL until that time.

So I'm wondering if its more beneficial to use a continuous connection that exists for the lifetime of the application (if possible) or to simply connect to sql before I intend to use it, do what the app needs to do then disconnect?

Answer 1

Connecting once and performing many queries will naturally be more efficient.

However, if performance isn't a major concern for your project, maybe aiming for simplicity in your code might be a better option (especially if you are the only connection to the database).

If you want to get clever, then maybe connect as and when you need to, then keep the connection alive until you stop making queries. Eg, drop the connection if there have been no queries for 30 seconds or something like that.

Answer 2

How many instances of this app will be connecting to MySQL? If it's just one, keeping a MySQL connection open for convenience shouldn't cause any problems, but remember there's a (configurable) limit to the number of MySQL connections you can have open to the server. In this case, I would recommend opening a connection, running whatever queries you need to run, and then closing it. Connecting per query adds more overhead as you add queries to your application.