[thelist] [mysql] condensing queries

Andrew Clover and-evolt at doxdesk.com
Tue Nov 4 04:42:36 CST 2003


kris burford <kris at midtempo.net> wrote:

> firstly, is it a good idea to create a single, but more complex query - or 
> should i stick to several simple ones?

If there is shared work done by both queries (eg. a subselect) there is a
case for combining them. (Trading off against code readability etc.)

This is not the case here:

> $sql1 = "select count(image_id) as imagecount from images where user = 
> '$user' ";

> $sql2 = "select count(link_id) as linkcount from links where user = 
> '$user' ";

The DBMS must go through both tables separately looking for matching user
columns; there is no optimisation where it can check both tables at once.

> simply combining them as: $sql = "select count(i.image_id) as imagecount, 
> count(l.link_id) as linkcount from images i, links l where i.user = '$user' 
> and l.user = '$user'";

> returns the value of images*links

Yes. The 'FROM x, y' clause doesn't 'add' the rows from x to the rows of y:
it forms a join where every possible row in x is joined to every possible
row in y. Normally you then restrict the join with a WHERE clause to
select only rows that are actually related. Otherwise you just end up with
an enormous (x*y) table. Then either of your 'count' clauses just total
up the number of rows in the great big join table.

Stick with the two queries, but:

> "where user = '$user' ";

Careful with that. Unless you're using magic quotes (which is in general not
a good thing at all), apostrophes and backslashes in the $user name can
cause you trouble. Look at eg. mysql_[real_]escape_string for stuff like
this.

-- 
Andrew Clover
mailto:and at doxdesk.com
http://www.doxdesk.com/


More information about the thelist mailing list