In my photo album site ( http://3e.org/pw/ , source at
http://3e.org/pw/source.php ) I allow users to 'and' together
conditions, such that they can say 'show me photos in this set AND
with this person AND at this location', and only photos that match all
three will be shown.

I end up generating a query that looks like this:

SELECT DISTINCT p_photos.pho_id,private 
FROM p_photos,p_photos_sets,p_photos_persons
WHERE 1=1
 AND p_photos_sets.pho_id = p_photos.pho_id
 AND p_photos_sets.set_id = 1
 
 AND p_photos_persons.pho_id = p_photos.pho_id
 AND p_photos_persons.per_id = 30 

 AND loc_id = 41

ORDER BY pho_date DESC

(the reason for the 1=1 is made clear in the source code)

The trouble is that that's creating a HUGE temporary table, which then
gets searched for matching entries. This takes a great deal of time
and memory.

There must be a more 'correct' way of phrasing this query to be less
resource-intensive!

---

Clarification of Question by placain-ga on 18 Jun 2002 17:19 PDT

I'm using mysql 3.23.44.

Dropping DISTINCT gave me a result set of many thousands of rows.

I realize it would be advantageous to build the FROM dynamically, but
I'm not sure how to do that without making horrid spaghetti-code.

Adding indexes seems to have helped the speed greatly.

---

Clarification of Question by placain-ga on 18 Jun 2002 17:25 PDT

cesium - dynamically building the FROM clause was easier than I
thought -- i'd been thinking about it wrong.

Check out the source now - it seems to be working great.

I still think there's a more 'correct' way to be doing all this,
though...?

what database are you using?

Try droping DISTINCT.

If you do select distinct, the whole query has to be evaluated, just to
drop duplicates. I assume that pho_id is unique in p_photos; the
other two tables are joined on pho_id, so the result set should already
be free of duplicates.

p.s. I like the 1=1, a ended up doing the same thing before, because there
     is no constant for true in SQL

Hope that helps

The source code at the referenced web site shows that there are no
indices on pho_id in the photos_persons and photos_sets tables.

Next, you will need to generate the FROM clause based on the input. 
For example, if $set_id is not specified, the current code will have
to do a full table scan on the photo sets and join those to the
desired records so they can be removed by DISTINCT.  When $set_id is
not specified, it is better not to list the photo_sets table in the
FROM clause.

Use the EXPLAIN command in mysql (section 5.2.1) to have mysql tell
you why the query runs slow.

Cs

Use LIMIT and OFFSET, which are not SQL standard (yet) but are
supported by many databases including mySQL.

See here:
http://www.mysql.com/doc/S/E/SELECT.html

and I'll quote from http://www.mysql.com/doc/L/I/LIMIT_optimisation.html:
"When the server uses temporary tables to resolve the query, the LIMIT
# is used to calculate how much space is required."

Another option might be to split up the query into several lightweight
queries.

For instance something like:

SELECT pho_id
FROM p_photos_sets
WHERE set_id = 1

-> save as a variable

SELECT ...
FROM p_photos
WHERE pho_id = <variable>

Same for other possible merges. Like this you avoid those enormous
table joins and however you might have several queries now for a
single request, it should still go a lot faster if you're working with
large tables. If your database supports it, you might also use
subqueries, to bundle everything in a single query - something like:

SELECT ...
FROM p_photos
WHERE pho_id = { SELECT pho_id FROM p_photos_sets WHERE set_id = 1}

MySQL doesn't support this however.

Hoper this helps,

rhansenne-ga.

Hai friends
I think, i can give you well soluction for this question because this
type  of proble i got it and i solve it.
If you have use MYSQL database very simple option it.
Just explained below
 
SELECT DISTINCT p_photos.pho_id,private  
FROM p_photos,p_photos_sets,p_photos_persons 
WHERE 1=1 
 AND p_photos_sets.pho_id = p_photos.pho_id 
 AND p_photos_sets.set_id = 1 
  
 AND p_photos_persons.pho_id = p_photos.pho_id 
 AND p_photos_persons.per_id = 30  
 
 AND loc_id = 41 
 
ORDER BY pho_date DESC LIMIT 0,50

here you will get corretct recrods from the table.
LIMIT is the parameter of the SELECT statement
0, is the starting position of the record.
50, No of records you need to take the sql query.

thank

You may be able to get better optimisation by using JOIN for the basic
table joins, something like:

SELECT DISTINCT p_photos.pho_id,private   
FROM p_photos LEFT JOIN p_photos_sets on (p_photos_sets.pho_id =
p_photos.pho_id) LEFT JOIN p_photos_persons ON
(p_photos_persons.pho_id = p_photos.pho_id)
WHERE 1=1  
 AND p_photos_sets.set_id = 1  
 AND p_photos_persons.per_id = 30   
 AND loc_id = 41  
ORDER BY pho_date