How expensive an operation is connecting to a Mysql database?

Question

In certain functions of the code, php will execute hundreds or in some cases thousands of queries on the same tables using a loop. Currently, it creates a new database connection for each query. How expensive is that operation? Would I see a significant speed increase by reusing the same connection? It could take quite a bit of refactoring to change this behavior and use the same database.

The php uses mysql_connect to connect to the database.

Just based on what I've said here, are there other obvious optimizations that you would recommend (I've read about locking tables for example...)?

EDIT: My question is more about the benefit of using a single connection, not how to avoid using more than one.

Answer 1

I use Zend_Framework and my database profiling shows that the connection itself takes nearly 10x longer than most of my queries. I have two different databases that I connect to, and only connect once to each for each request.

I'd say reconnecting for every query is poor design, but the question of refactoring is more complex than that. Questions that need to be asked:

1. Are there current performance problems?
2. Have you done code profiling to narrow down where the performance issues are occurring?
3. How much time will be required for this refactoring? Take into account the testing involved, not just coding time.