Is it true that writing the database name in every query is faster than calling mysql_select_db() on every page load?

Question

Hi, I work at a fairly big website; we have ~400-500 million page views a month. We use PHP and MySQL.

Currently our page flow works like this (semi pseudo code for clarity):

mysql_connect();
mysql_select_db('red');
mysql_query('SELECT * FROM apples');
mysql_query('SELECT * FROM cakes');

One of my co-workers suggested that mysql_select_db slows down page loads and increases the load of the DB server, and suggested to change our "flow" to be like:

mysql_connect();
mysql_query('SELECT * FROM red.apples');
mysql_query('SELECT * FROM red.cakes');

Is it true that it will make a difference? I'm especially concerned about the time it will take to make this change; will we see any noticeable results?

Is it worth it?

Answer 1

Benchmark:

$ cat 1.php 
<?php
mysql_connect('localhost') or die(mysql_error());
mysql_select_db('test') or die(mysql_error());
mysql_query('SELECT SQL_NO_CACHE * FROM foo') or die(mysql_error());
mysql_query('SELECT SQL_NO_CACHE * FROM bar') or die(mysql_error());
?>
$ cat 2.php 
<?php
mysql_connect('localhost') or die(mysql_error());
mysql_query('SELECT SQL_NO_CACHE * FROM test.foo') or die(mysql_error());
mysql_query('SELECT SQL_NO_CACHE * FROM test.bar') or die(mysql_error());
?>
$ time ( for i in {1..100} ; do php 1.php; done;)

real    0m3.554s
user    0m2.300s
sys     0m1.188s
$ time ( for i in {1..100} ; do php 2.php; done;)

real    0m3.555s
user    0m2.292s
sys     0m1.208s

I call bullshit.

Is your coworker perhaps confused with the mysql command-line client, which will load all table & columnnames if you switch to a database (which can be avoided with the -A switch)?

Answer 2

i don't know for sure about your question but i doubt it would make significant difference.

but there's other things that can make. i suggest you write all the column names instead of using *. this i know for sure that will speed up your queries.

ex:

mysql_query('SELECT id, size, color FROM apples');

other thing you can do is to use LIMIT correctly. for instance, if you are selecting an user from database, and you KNOW for sure that it's unique, use LIMIT 1 at the end of the query.

ex:

SELECT id, username, access_level FROM users WHERE id = ? LIMIT 1

Answer 3

I doubt very much it would make a difference performance wise if you add the database name.

Persistent connections and refactoring your queries to never, EVER select * would be a good first step, I think. Then you might want to think about using query caching, and having a look at your slow query log. Those are going to help you more than some small semantic difference like specifying the schema name ever could.