Which is more optimized?

[CoffeeMugDude]

Hi folks,

Which is better, more optimized, less load on the mySQL server... ?

1. One query to a single table (dogs) listing all records, including "ownerID". Then, quick lookups to another single table by primary key ("SELECT owner_name FROM owner WHERE ownerID = '123'")

or...

2. One single query, joining the 2 tables (on an index)

Thanks :-)
David

[Hunkorama417]

I actually think the join is quicker because it's one query, where as the other is two queries.
------------------
Andrew
http://www.digi-FX.net
andrew@digi-fx.net

[CoffeeMugDude]

OK, here's a trickier one...

Hypothetically speaking, say we have table "dogs", with fields "dog_name", "ownerID", "trainerID"...

Both "trainerID" and "ownerID" are lookups to the "people" table, and both exist in every "dog" record.

I can use the following SQL to get the name of either the owner or the trainer, but not both...

SELECT dog_name, people_name, from dog LEFT OUTER JOIN people ON dog.ownerID = people.peopleID


Any ideas how I could look up both the owner AND the trainer names in the same query? (I'd have to do two joins to "people" somehow?)

Thanks :)
David

[Hunkorama417]

What are the fields you want returned?

[CoffeeMugDude]

Well, in a single query, I want "dog_name" (from table dog), "people_name" (from table people, joined on ownerID), and "people_name" again (also from table people, but joined on trainerID this time)

So we have 2 tables, "dog" and "people"...

Table "dog" has 3 fields: dog_name, ownerID, trainerID
Table "people" has 2 fields: people_name, peopleID

- David

[janderk]

Any ideas how I could look up both the owner AND the trainer names in the same query?
You need a UNION to combine the two.

Jan Derk

[PaulKroll]

MySQL doesn't have UNION, at least not yet.

Not having test tables set up, this might burst into flames, or it might work as planned:
SELECT dog_name, people_name from dog LEFT OUTER JOIN people ON dog.ownerID = people.peopleID OR dog.trainerID = people.peopleID
But you won't be able to tell which ones are owners and which are trainers, this way: you'll have names of dogs and names of people that can be either.

[Rich]

SELECT dogs.dog_name, owners.owner_name, trainers.trainer_name
FROM (dogs INNER JOIN owners ON dogs.owner_id = owners.owner_id) INNER JOIN trainers ON dogs.trainer_id = trainers.trainer_id

<edit>
The above only shows dogs that have both an owner and a trainer. If you want to list all dogs and owners/trainers if they have one, use:

SELECT dogs.dog_name, owners.owner_name, trainers.trainer_name
FROM (dogs LEFT JOIN owners ON dogs.owner_id = owners.owner_id) LEFT JOIN trainers ON dogs.trainer_id = trainers.trainer_id;

</edit>

<reedit>
I noticed after posting the above that you do not have a 'trainers' table and that the trainer_id points back to the owner table. So, in this case, you can use:

SELECT dogs.dog_name, owners.owner_name, owners_1.owner_name AS Trainer
FROM (dogs LEFT JOIN owners ON dogs.owner_id = owners.owner_id) LEFT JOIN owners AS owners_1 ON dogs.trainer_id = owners_1.owner_id

As in the previous examples, if you only want dogs that have both an owner and a trainer, substitute 'INNER JOIN' for the 'LEFT JOIN' in the select statement.

</reedit>

Rich

[This message has been edited by Rich (edited 05-21-01@12:15 pm)]

[janderk]

MySQL doesn't have UNION, at least not yet.
Sorry for the misinformation. It clearly shows that I'm not current with MYSQL (just plain SQL). Me and my big mouth...

Have to say that I'm a little dissappointed to learn that MYSQL does not support such an important SQL statement as UNION. A quick search showed that there are some dirty workaround and that MYSQL 4.x will support UNIONs and Sub-queries.

http://users.starpower.net/rjhalljr/MySQL/sql.html#union

Jan Derk
-- The wait for 4.x is on --

[cmahnken]

You can use the same table multiple times by aliasing the table:


SELECT dogs.dog_name,
[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] owner.people_name as owner_name,
[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] trainer.people_name as trainer_name
FROM (dogs INNER JOIN people AS trainer ON dogs.trainerID = trainer.id)
[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] INNER JOIN people AS owner ON dogs.ownerID = owner.id


------------------
Chris Mahnken

[CoffeeMugDude]

Thanks guys, I think aliasing will work - I'll try it out tonight :)

[CoffeeMugDude]

Aliasing worked like a charm.

Thanks all! :)