Is it true that writing the database name in every query is faster than calling mysql_select_db() on every page load?
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.app开发者_JAVA百科les');
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?
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)?
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
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.
精彩评论