Re: Index Choice Problem

Nevermind the reply, blonde moment on the ordering...

This works :)

Thanks

On 2/18/06, Adam Alkins <adam(dot)alkins(at)gmail(dot)com> wrote:
>
> Unfortunately I'm using 8.0.4 and this is for a government website, I only
> get so many maintenance windows. Is this the only workaround for this issue?
>
> I did make a test index as you described on my test box and tried the
> query and it used the new index. However, ORDER BY forum_id then
> last_post_time is simply not the intended sorting order. (Though I'm
> considering just SELECTing the topic_last_post_time field and resorting the
> results in the script if this is the only workaround).
>
> - Adam
>
> On 2/18/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Adam Alkins <adam(dot)alkins(at)gmail(dot)com> writes:
> > > SELECT t.topic_id
> > > FROM phpbb_topics AS t
> > > WHERE t.forum_id = 71
> > > AND t.topic_id NOT IN (205026,
> > 29046, 144569, 59780, 187424,
> > > 138635, 184973, 170551, 22419, 181690, 197254, 205130)
> > > ORDER BY
> > t.topic_last_post_time DESC
> > > LIMIT 23 OFFSET
> > 0
> >
> > If you're using 8.1, you'd probably find that an index on (forum_id,
> > topic_last_post_time) would work nicely for this. You could use it
> > in prior versions too, but you'd have to spell the ORDER BY rather
> > strangely:
> > ORDER BY forum_id desc, topic_last_post_time desc
> > The reason for this trickery is to get the planner to realize that
> > the index order matches the ORDER BY ...
> >
> > regards, tom lane
> >
>
>
>
> --
> Adam Alkins
> http://www.rasadam.com
> Mobile: 868-680-4612
>

--
Adam Alkins
http://www.rasadam.com
Mobile: 868-680-4612