On Nov 18, 2008, at 5:48 PM, Ryan Wells wrote:
> While looking through our data layer code today, I ran across this
> FROM tasks
> LEFT JOIN clients ON tasks.ClientId = clients.ClientId
> LEFT JOIN iteminfo ON tasks.Id = iteminfo.ItemId
> LEFT JOIN changelog ON tasks.Id = changelog.ItemId
> LEFT JOIN ticklers ON tasks.Id = ticklers.RelatedId
> WHERE tasks.Id = '123456';
> (I've cleaned it up so it's easier to read.)
> The basic data structure is that we have a todo list that contains a
> list of tasks which may or may not be associated with clients,
> items, log entries, or ticklers (scheduled reminders).
> The query works as intended: it returns a result-set with all the
> necessary data to display in the todo list. The performance is not
> a major concern, although it can be slow for large lists.
> Since it works, my question is really more about principles: Given
> that each of the tables in question will contain tens of thousands
> of rows, is a nested join really the best way to approach this?
To answer this better we need to know the exact relations between each
For example, does each task have 0 or more clients, or 1 or more
If it's 1 or more, then you can properly use a JOIN instead of a LEFT
JOIN, this would make the operation faster I believe.
I hope I did explain myself correctly.
In response to
pgsql-sql by date
|Next:||From: Mark Roberts||Date: 2008-11-18 23:22:09|
|Subject: Re: Left Join Question|
|Previous:||From: Ryan Wells||Date: 2008-11-18 22:48:33|
|Subject: Left Join Question|