As a "newbie" of one kind to dynamic web page creation and database usage, I'd like to know your opinions about this. Many examples I've seen often consist of a single file (for example php file), and thus a database connection is established and closed within the same file. Now usually dynamic web site has more than one file, but let's say I still needed to access a database from different files; would it be sensible to always, within a file (like list_some_items.php), open a database connection and once needed data is fetched, close it; then with another file (like show_some_information.php) do the same open-fetch-close-thing on the database -- or -- would it be better to establish a database connection right after a session is started for the user and remain the connection open and close it only when the user leaves, and session is destroyed?

Like I said, I'm fairly new to these things, as of now working with php and MySQL, so bear me. I'll possibly try other things in the future, but I guess this question is somewhat "common"; is there a huge difference I don't see right away, security problems having the database connection open all the time the user is in the site, maybe efficiency problems? Or is it even possible, or sane, to maintain a db connection during a session?

Thanks for opinions!

As far as I know the open/close process to a database it is kind of slow. I mean it is slow for a couple connections each second - if your site is not traffic intensive you will probably not notice.

The best is to use connection pooling. I do not know how is this implemented in php as I used just java, but the main idea is that the server or somekind of manager opens a bunch of connections to the database and when a 'page' needs a connections it borrows it from the pool. When it doesn't need it anymore it 'release' it and the pool can reuse it.

A quick search on the web revealed this, maybe it is helpfull to you: http://www.apachetutor.org/dev/reslist

Thanks for the information and the link, I'll read it; this is probably what I'm after. The site isn't probably going to be too "traffic-intensive", borrowing your words, but I still somehow dislike the idea of having to open&close a separate connection every time a database-needing page is opened.

Hi

MySql is very fast on opening and closing connections. On a busy site, the problem is usually the number of database connections, not the time it takes. A typical scripts opens a connection, does some queries, outputs html and then closes. To scale things better, you can open the connection, do queries, close the connection and then output html.

The problem is usually slow clients keeping the connections open while getting the html. I think keeping the connection open in the session would just make things worse, and I've never seen LAMP solutions doing it.

Now this sounds interesting. So you're saying it's ok to have a connection open&close once per php page (when needed), as far as the html code creation etc. are outside this? (actually this is how I always do things, never even thought the other way around)

Well, maybe there won't be speed problems since the site is and probably stays pretty small, including users that visit it..but anyway I have to do some more searching about the speed thing.

I think most LAMP solutions don't even consider this, they just open the database connection and it gets closed when the script is finished.

Try benchmarking your site with e.g. "ab" (Apache benchmark). I found out, putting a mysql_close() in the right place can help a lot.

I still think there will be a performance improvement if you use connection pooling (if it is less work for the CPU it should be faster). Most of the application do not need that improvement as they run on fast servers/don't have bilions of request per seconds.

You always can test yourself - and if you do, please post the results. I'm also curious what would be the speed difference, but probably not enough to install apache & php.