Selecting from multiple tables

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
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Selecting from multiple tables

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 ;)
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Post 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 :)
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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.
Post Reply