Re: join optimization problem

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Rod,

thanks for that - I tried it out, fixed the minor typos and it ran at almost
the same speed! Any other ideas?

cheers,
Toby

On Friday 01 August 2003 10:33, Rod Taylor wrote:
> How about this? It should spit out the same number, but be quite a bit
> quicker at it. Untested of course...
>
>
> select t.thread_id,
> t.forum_id,
> t.thread_title,
> t.thread_owner,
> t.thread_owner_id,
> t.date_created,
> t.thread_hits,
> t.poem_reference,
> t.bArchived,
> count, maxdate
> from fbof_thread t
> LEFT OUTER JOIN
> (select thread_id
> , count(msg_id) as count
> , max(date_created) as maxdate
> from msg
> group by thread_id
> ) as tab ON m.thread_id = t.thread_id
> where t.forum_id = 1
> and t.bArchived = 0
> and t.bSticky = 0
> order by t.date_created desc
>
> > can anyone point out to me where I'm going wrong here? I can't seem to
> > make it faster for the life of me.... I've tried adding indices on all
> > the main fields etc but nada. I'm not subscribed the list currently so
> > please reply to my address as well as the list.

- --

- --------------------------------

Life is poetry -
write it in your own words

- --------------------------------

Toby Tremayne
Code Poet and Zen Master of the Heavy Sleep
Senior Technical Consultant
Lyricist Software
www.lyricist.com.au
+61 416 048 090
ICQ: 13107913

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux)

iD8DBQE/LOI36KYxVcB16DkRAg7RAKCIALKF4TExS9Q38WiM8jTzRxFctgCgttI3
jbfhQ4GrW2BKPU5uhRoK4rc=
=F+c9
-----END PGP SIGNATURE-----