FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Optimizing MySQL joins





leontius
Given the following table structure:
Code:
CREATE TABLE user (
   uid INT(11) auto_increment,
   name VARCHAR(200),
   PRIMARY KEY(uid)
);
CREATE TABLE user_profile(
   uid INT(11),
   address VARCHAR(200),
   PRIMARY KEY(uid),
   INDEX(address)
);


Which join query is more efficient: #1,
Code:
SELECT u.name FROM user u INNER JOIN user_profile p ON u.uid = p.uid WHERE p.address = 'some constant'


or #2:
Code:
SELECT u.name FROM user u INNER JOIN (SELECT uid FROM user_profile WHERE p.address = 'some constant') p ON u.uid = p.uid


How much is the difference in efficiency? (sorry if there's some flaw in the query, I didn't test it but you should get the point)
jmraker
I'd say the first one because the 2nd one is 2 sql statements (one inside the other). If you want to really know how much faster one is, you just add a microsecond timer to your code and time them both inside 2 loops that runs each a few thousand times.

Code:

StartTimer();
for($a = 0;$a < 3000;$a++){
 // Insert SQL1 here
}
echo 'Time: ' . StopTimer() . "<br>\n";

StartTimer();
for($a = 0;$a < 3000;$a++){
 // Insert SQL2 here
}
echo 'Time: ' . StopTimer() . "<br>\n";


function microtime_float(){
  list($usec, $sec) = explode(' ', microtime());
  return ((float)$usec + (float)$sec);
}

function StartTimer(){
  global $__timer;
  $__timer = microtime_float();
}

function StopTimer(){
  global $__timer;
  $stop = microtime_float();
  return round($stop - $__timer, 3);
}
albuferque
You can check it with Sql-Wave, it has many performance tools. I think it's the IBExpert for MySQL Very Happy
dwxco
I agree that the first approach should be faster. In the second approach, the first query has to wait for the results of the second query. To me, queries that run in less than .01 seconds are generally fine, even if poorly written (since the goal is to get data back fast). But once you run into scale problems, then you need to identify the poorly performing queries and optimize those.
Related topics
mysql connection question
php admin and mysql admin console
Mysql And PHP HELP PLZ
Do you use a CMS script?
Question about MySQL!
PHP, MySQL...
PHP Book (Php and Mysql for Dynamic Web Sites)
few mysql questions
E-Cards with PHP & MySQL
What exactly does indexing mysql do?
mysql syntax error I can't find why
need help with mysql joins
Optimizing a mySQL database
MYSQL Normalization double join
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.