Two selects or one join?

mnuzum

I'm trying to keep efficiency as a major focus in my applications. So here's a simple question.

In general, is it better to do two simple selects or one join?

If I have tables like this:
Table1
id1
name1

Table2
id2
id1
name2

Where Table2.id1 is a foreign key for Table1.id1.

Let's say that I have a shopping cart or similar system. I want to display name1 on the top of the page and then show a list of all items that are joined to it.

I could do two simple queries, "SELECT name1 FROM Table1 WHERE id1=$id1" and then "SELECT name2 FROM Table2 WHERE id1=$id1".

Or I could join them like this, "SELECT name1,name2 FROM Table1,Table2 WHERE Table1.id1=Table2.id1 AND Table1.id1 = $id1".

With this simple result set, both would be pretty fast. However real world cases are always much more complex than this.

So if I'm designing an application and I want my response and load on the webserver and database server to be as fast as possible, which should I use (when all else is equal)?

I've thought it through in my head and had two conclusions.
First, Doing a query certainly has some overhead. PHP and/or the database have to keep track of a recordset for each query.

Secondly, Doing a join has some overhead as well. Additionally, every record in the joined recordset contains two fields. One of these fields is only needed one time at the onset.

I've heard that there are tools that will annalyze your query and help you understand the "costs" associated with it. I've never used one before but I'd like to. If there's a tutorial out there or an easy to understand tool, pointers would be appreciated. FYI, I typically use PostgreSQL or MySQL for my applications (Postgres being used more often).

Anon

If the tables are indexed properly and since you will only need one piece of data from the first query I would say to use two separate queries in this case. There is no real need to join the data here except to get one result set and that result set will contain extra data that is not needed.

In Oracle an EXPLAIN PLAN can be used for a query to display the execution path and cost of a query, but I don't know of a simular thing for MySQL or PostreSQL.

Anon

The equivalent to EXPLAIN PLAN in Oracle is .......(big breath of air :o) )

EXPLAIN <select statement> in mysql.

http://ftp.plig.org/pub/mysql/doc/E/X/EXPLAIN.html

Cheers

Justin

[deleted]

There is only one way to find out which works better in your case, and that is to try it out.

It is impossible to say 'this is better' because it depends very much on the size of your tables, the type of data, the type of relation and the overal server-load.

Remember, doing an extra query has overhead on both PHP and the database and the network (if db and php are seperated), but the tabeles required for a JOIN are usually already in the database's memory, so that may work out to be faster in the long run.

PostgreSQL also has the 'explain' command, and gives very clear information about how the query is handled what it expects the 'cost' will be. (lower cost is better)

In postgreSQL you can also try using stored procedures to let you do more than one query on the database while doing only one pg_exec() in PHP.