SQL optimization

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

SQL optimization

Post by alex.barylski »

I'm by no means an expert in SQL, but...

I once read that in order optimize queries you should keep them as brief as possible (Select only specific fields, reduce the amount of table joins, etc...)

Now my question is...

Assuming I have three tables (table1, table2, table3) and currently have a simple SQL JOIN statement to pull data from each table returning a single resultset...

Code: Select all

SELECT * FROM table1, table2, table3 WHERE table1.pkid = table2.pkid AND table2.pkid = table3.pkid AND table1.pkid = 12
Not sure if the above is syntactically correct but i'm sure you'll get the idea...

Anyways...please correct me if I'm wrong, but would the above be faster if I pulled the individual record from each table using three seperate queries:

Code: Select all

SELECT * FROM table1 WHERE pkid = 1
SELECT * FROM table2 WHERE pkid = 1
SELECT * FROM table3 WHERE pkid = 1
And then merged the results using PHP??? Not sure how I would go abouts doing that, but i'm sure it's possible - so long as multi-result queries yield the same number of results...???

Anyways the important question here is...is three seperate queries faster than a single query using implicit JOIN's...???

I'm not looking for practical here...i'm looking for reality...i'm not going to use theo above technique...I just need to know :)

Cheers :)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Benchmark

Code: Select all

function query() {
		$result1 = mysql_query(
		  'SELECT * FROM table1, table2, table3 WHERE table1.field1 = table2.field1 AND table2.field1 = table3.field1 AND table1.field1  = \'blah1\''
		  ) 
		  or die(mysql_error());
		  
		$row1 = mysql_fetch_assoc($result1);
	}
Average: 0.03722734 through 50 times (x 100) == 5000 loops.

Code: Select all

function query() {
		$result1 = mysql_query('SELECT * FROM table1 WHERE field1  = \'blah1\'') or die(mysql_error());
		$result2 = mysql_query('SELECT * FROM table2 WHERE field1  = \'blah1\'') or die(mysql_error());
		$result3 = mysql_query('SELECT * FROM table3 WHERE field1  = \'blah1\'') or die(mysql_error());
		
		$row1 = mysql_fetch_assoc($result1);
		$row2 = mysql_fetch_assoc($result2);
		$row3 = mysql_fetch_assoc($result3);
	
		$result = array_merge($row1, $row2, $row3);
	}
Average: 0.03508358 through 50 times (x 100) == 5000 loops.


As you can see on simple queries working with small amounts of data there is a very slight difference.. mind you the larger your queries become and the more data you are working with a query with joins will become more profficient than a bunch of smaller queries.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Ya, it's probably best to do just one query. The biggest jump in performance I've seen happens after I index columns I'm matching on. So, set up an index on table1.field1, table2.field1 and table3.field1. There should be a noticable jump in speed if your query is taking a long time.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply