Page 1 of 1

SQL optimization

Posted: Sun Nov 06, 2005 11:14 am
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 :)

Posted: Sun Nov 06, 2005 12:54 pm
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.

Posted: Mon Nov 07, 2005 10:37 am
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.