Best Practice: Multiple DB Connections vs. Single Connection for multiple MySQL schemas?

Ignition
1

Hi,

I am currently working on an Ignition 8.1.42 project and I have a question regarding database architecture best practices.

I have multiple databases (schemas) hosted on the same MySQL instance. I am wondering which approach is more efficient and performant:

  1. Single Gateway Connection: Create one Database Connection in the Gateway and use dot notation in my queries (e.g., SELECT * FROM other_db.table).

  2. Multiple Gateway Connections: Create a dedicated Database Connection for each schema.

My main concerns are resource management (connection pooling), ease of maintenance, and overall Gateway performance. Are there specific scenarios where one approach is significantly better than the other in Ignition?

Thanks

2

If you have different connections for different schemas, then you can also have different users for each connection and all those users can have the corresponding schema as their default. This way you can use a single query that is schema agnostic and reuse it across all your schemas with the only difference being the DB Connection used. This would benefit you if your different schemas have the same DB table structure etc.