[thelist] SQL question: Will this require a sub-select?

Adelle Hartley adelle at akemi.com.au
Mon Sep 8 04:53:00 CDT 2008


Bill Moseley wrote:
> On Fri, Sep 05, 2008 at 08:13:40PM -0400, r937 wrote:
>>> I'm not seeing any way around the sub-select.
>> why would you want a way around it?
>>
>> that query looks okay to me
>>
>> what happened when you tested it?
> 
> It's considerably slower than without the sub-select, of course.
> About four times slower, it turns out.  (The EXPLAIN shows a few
> sequence scans and I'm looking into why the indexes are not being
> used.)
> 
> I'm looking at ways to improve, so my sanity check first
> was to see if anyone here might have a more efficient way to write it.
> 
> As always, the problem is a bit more involved.
> 
> First, in real life it's a query that runs very often (to check if
> some threshold has been exceeded), but changes rarely.  Although I try
> and avoid de-normalization, I might want to save the counts in a
> separate table and update that table from the select below only when a
> new item is inserted or updated.

An acceptable last resort if you do it right:  ie.  If you're going to 
write triggers to maintain the counts, make sure the triggers work 
correctly when a single query adds or removes multiple rows.

If you want month totals and book totals to appear in the same query 
then you will need either a cube or a sub-select.

You can avoid either, if you only include book totals in the query, and 
calculate the month totals in your application.  That's if I've 
understood your question correctly.

In that case, make the chapter table the first table to be mentioned in 
the query.  As another poster has already mentioned, joining tables in 
the many-to-one direction is practically cost free if the right indexes 
are there.

Adelle.




More information about the thelist mailing list