Re: Query optimisation

[Naz Gassiep <naz@xxxxxxxx>]

As you can see, they all are the same table, just repeatedly joined with aliases. The images table has several fields, each one referring to a different sized version of the image. It then has to join against the files table for each size to get the file that corresponds with that image version.
- Naz.


Craig Ringer wrote:

Naz Gassiep wrote:

  

                JOIN files imageid_file ON (images.imageid =
imageid_file.fileid)
                JOIN files size120_file ON (images.size120 =
size120_file.fileid)
                JOIN files size240_file ON (images.size240 =
size240_file.fileid)
                JOIN files size420_file ON (images.size420 =
size420_file.fileid)
                JOIN files size600_file ON (images.size600 =
size600_file.fileid)
                JOIN files size800_file ON (images.size800 =
size800_file.fileid)
                JOIN files size1024_file ON (images.size1024 =
size1024_file.fileid)
                JOIN files size130sq_file ON (images.size130sq =
size130sq_file.fileid)
                JOIN files size240sq_file ON (images.size240sq =
size240sq_file.fileid)
                JOIN files size420sq_file ON (images.size420sq =
size420sq_file.fileid)
                JOIN images_sites ON (images_sites.imageid =
images.imageid)
    

That's a whole lot of joins.

Do all those tables have the same fields? Can you unify them into one
table with an additional field like "imagesize" and use an appropriate
WHERE clause when looking up the table?

--
Craig Ringer