Page 1 of 1
Selecting from multiple tables
Posted: Mon Feb 06, 2006 7:05 pm
by Luke
OK, let's say I have 5 tables...
manager, hours, policies, rent, and properties (which ties them all together). Now let's say I need to find out all manager, hours, policies, and rent information for property #1...
What is the advantage of doing this with one query as opposed to doing it with many?
I have it set up to just do an individual query for the information I need from each table. Is this the wrong way to do this?
for example:
Code: Select all
SELECT * FROM properties
which provides me with the necessary ids to perform the following queries...
SELECT * FROM manager WHERE id = 2
SELECT * FROM hours WHERE id = 4
SELECT * FROM policies WHERE id = 2
SELECT * FROM rent WHERE id = 7
Posted: Mon Feb 06, 2006 7:41 pm
by feyd
performing a join with all those tables may cause serious load on your database server. You can, and should test it however to find out how your server will react.
Read up on INNER JOIN, LEFT JOIN, and so forth.
Posted: Mon Feb 06, 2006 8:29 pm
by Luke
Well I have done some reading on joins and such... just never found any information that says whether or not this is just as efficient:
Code: Select all
$tables = array(1 => 'manager', 5=> 'hours', 21 => 'rent', 20 => 'policies');
foreach($tables as $id => $table){
$sql[$table] = "SELECT * FROM " . $table . " WHERE id = " . $id;
}
And then running each of those queries individually...
Or something along those lines.
Posted: Mon Feb 06, 2006 8:53 pm
by timvw
I would expect that one query is executed faster than n queries... Anyway, if you want to know the answer you should run a couple of tests... And then share those results with us

Posted: Mon Feb 06, 2006 9:43 pm
by jwalsh
I'm curious to see the test results. I find myself jumping from one method to the other, and I'd like a justified reason to remove some chaos from my code

Posted: Mon Feb 06, 2006 10:42 pm
by Luke
well the only problem is that my database is TINY... I mean like 5 records in each table. The only reason I chose to use a database at all is because of the relational use. I can more easily allow for my customer to administer a database than a text file. 5 records will produce some pretty quick results no matter what... will a test really show a difference between the two? I guess I will have to find out.
Furthermore, I do not know where to begin as far as turning that into one query. Oh well... this will be a learning experience.
Posted: Mon Feb 06, 2006 10:45 pm
by josh
Actually understanding the mechanics of a JOIN are critical before you can start guessing at which will be faster.
Let's say you have a table of locations, and a table of photos
Multiple photos can belong to each location and one photo per location will be flagged as the default.
Now let's say you have the location's id and you need the location name and the default photo. Depending on the size of your photos a
Code: Select all
left join on `photos`.`location` = `locations`.`id` and `photos`.`default` = 1
Can examine redundantly, meaning if you have 20 locations to select default photos for it is joining the entire photos table onto the locations table for each location, multiple queries for each photo would be faster in this scenario. In the case of one row being returned the JOIN almost always wins (again there are exceptions). I discovered this on accident, so it could possibly be a bug or flaw in my table structure but I doubt it.