| From: | Naz Gassiep <naz(at)mira(dot)net> |
|---|---|
| To: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Query optimisation |
| Date: | 2008-04-07 05:44:33 |
| Message-ID: | 47F9B4C1.9070602@mira.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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.<br>
- Naz.<br>
<br>
<br>
Craig Ringer wrote:
<blockquote cite="mid:47F9A67A(dot)9090903(at)postnewspapers(dot)com(dot)au"
type="cite">
<pre wrap="">Naz Gassiep wrote:
</pre>
<blockquote type="cite">
<pre wrap=""> 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)
</pre>
</blockquote>
<pre wrap=""><!---->
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
</pre>
</blockquote>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 1.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Alban Hertroys | 2008-04-07 06:05:05 | Re: select distinct and index usage |
| Previous Message | Tom Lane | 2008-04-07 04:53:06 | Re: Query optimisation |