I'm glad you got this faster however should the FB query optimiser
know that in this case it would be too slow to use the index?

Giulio

--- In firebird-support@yahoogroups.com, "Todd Pinel"
<todd_pinel@y...> wrote:

> Wow, you were right the execute time dropped from ~40s to ~20s. I'm

happy

> with that! I have some other things I need to tweak before I get

too hung

> up on performance like this. This was great! Now could someone

explain why

> this worked the way it did?;) Also I have been trying to be very

selective

> in where I place my own indexes, I've found its only helped me in a

couple

> select places and I can justify them due to the performance benefit

they've

> given. I have assumed while building my DB that as I create foreign

keys

> between tables indices are created automatically. The indices you

see in

> use in the plan below are indices created from adding foreign keys,

should I

> take them out? (I've always assumed an index was good, with negative
> performance if you went overboard with them) Thanks for the great tip,
> Todd.
>
> _____
>
> From: Svein Erling Tysvær [mailto:svein.erling.tysvaer@k...]
> Sent: Friday, February 11, 2005 2:09 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: Insertion speed
>
>
> --- In firebird-support@yahoogroups.com, "Todd Pinel" wrote:
> > SELECT BP.BUYER_NO
> > FROM BUYERPUBLICATION BP
> > WHERE BP.PUBLICATION_NO = :PUBLICATION_NO
> > AND NOT EXISTS(SELECT IM.BUYER_NO
> > FROM ISSUESMAILED IM
> > WHERE IM.BUYER_NO = BP.BUYER_NO
> > AND IM.ISSUE_NO = :ISSUE_NO)
>
> Hmm, that looks pretty OK!
>
> > PLAN (IM INDEX (INTEG_132,INTEG_177)) PLAN (BP INDEX
> > (FK_BUYERPUBLICATIONPUBLICAT))
>
> Ouch, I dislike using indexes for both buyer_no and issue_no. Assuming
> that issue_no is far less selective than buyer_no (hopefully, you sell
> 100000 copies of each issue ;o), try changing one line to
>
> AND IM.ISSUE_NO+0 = :ISSUE_NO
>
> and you should observe a - hopefully dramatic - improvement (except
> for issues being sold to a handful of buyers).
>
> HTH,
> Set
>
>
>
>
>
>
>
> Yahoo! Groups Sponsor
>
>
> ADVERTISEMENT
>
>

<http://us.ard.yahoo.com/SIG=1293si76b/M=298184.6018725.7038619.3001176/D=gr

>

oups/S=1705115386:HM/EXP=1108199369/A=2532114/R=2/SIG=12km4vtut/*http:/clk.a

> tdmt.com/NFX/go/yhxxxnfx0020000014nfx/direct/01/&time=1108112969454483>
>
>
>

<http://view.atdmt.com/NFX/view/yhxxxnfx0020000014nfx/direct/01/&time=110811

> 2969454483>
>
>
>

<http://us.adserver.yahoo.com/l?M=298184.6018725.7038619.3001176/D=groups/S=

> :HM/A=2532114/rand=521123210>
>
> _____
>
> Yahoo! Groups Links
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>

<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
> <http://docs.yahoo.com/info/terms/> .
>
>
> [Non-text portions of this message have been removed]