Any way to hold a connection to mysql database in PHP?

Question

Im trying to optimize some of my PHP code. I found out that most time of my PHP script is spent during the connection to my mysql database at the beginning of the script.

I only connect to the database once at the beginning and close the database connection at the end of the script.

But for each user requesting this page a new connection has to be established.

Is there a way to hold a reference to the database and share it for all requests?

"I figured that most time..." you figured how? Did you actually test it? Doing this sort of thing is useless without testing where the bottleneck is. — PeeHaa, Mar 03 '13 at 13:54

score 5 · Answer 1 · edited Mar 03 '13 at 14:01

5

Yes, you can achieve that.

If you're using the MySQLi extension (the old one without the i at the end is outdated!), you can create a persistent connection by passing p: as a prefix to the hostname when creating the instance:

See mysqli::__construct.

_{If you're really using the old MySQL extension, there's mysql_pconnect for persistent connections}.

Alternatively, if you're using PDO, then you can use setAttribute() to use PDO::ATTR_PERSISTENT.

Documentation on using persistent connection in PDO.

edited Mar 03 '13 at 14:01

ComFreek

29,044
18
104
156

answered Mar 03 '13 at 13:54

hjpotter92

78,589
36
144
183

Looks like someone took the time to revenge vote the both of you? – PeeHaa Mar 03 '13 at 14:01
I upvoted your answer. Is PDO nowadays the right way to go? I will test it. – Sebastian Oberste-Vorth Mar 03 '13 at 14:05
@SebastianOberste-Vorth See [this question](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons) for example. The main aspect is that you can use different RDBMS with PDO. – ComFreek Mar 03 '13 at 14:08
@ComFreek That's the part of PDO which is actually overrated imho. It's named parameters and a nicer API. If you switch RDBMS you have to change you queries either way. – PeeHaa Mar 03 '13 at 14:10
@SebastianOberste-Vorth Yes, all `mysql_*` have been deprecated. Take a [reading of this](http://de3.php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated) to know the reasons in details. – hjpotter92 Mar 03 '13 at 14:11
@PeeHaa Of course, there are several advantages which just cannot be expressed in one single comment ;) I would start with PDO when creating a new project. – ComFreek Mar 03 '13 at 16:08

score 2 · Accepted Answer · answered Mar 03 '13 at 14:02

You can make your connection persistent (for PDO use PDO::ATTR_PERSISTENT => true), but my recomandation is to find out why your script is spending a lot of time connecting to mysql and make some improvments there.

Consider this:

Use MySQL server IP instead of hostname, to eliminate time needed to resolve dns.
Disable mysql autocompletition: (comment skip-auto-rehash from my.cnf)
If you make a lot of queries which will return the same thing over and over again, consider using a caching system to cache for query results.
Post your code on codereview.stackexchange.com to benefit from others expertise on further improving your code.
To further debug the problem, connect from console to MySQL server and check the time needed to open connection, change database, select, etc.

Any way to hold a connection to mysql database in PHP?

2 Answers2