Re: order by index, and inheritance

On Thu, 2004-04-22 at 07:02, Michiel Meeuwissen wrote:
> Rod Taylor <pg(at)rbt(dot)ca> wrote:
> > The scan is picking the best method for grabbing everything within the
> > table, since it is not aware that we do not require everything.
>
> Hmm. That is a bit silly. Why does it use the index if select only from
> mm_mediasources?
>
> > You can explicitly tell it what you want to do via:
> >
> > SELECT *
> > FROM (SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20
> > UNION SELECT * FROM <subtable> ORDER BY number DESC LIMIT 20) AS tab
> > ORDER BY number DESC LIMIT 20
>
> I think you meant 'only mm_mediasources', and btw order by and limit are not
> accepted before union, so the above query does not compile.

Yes, I did mean only. Try putting another set of brackets around the
selects to get ORDER BY, etc. accepted. You can add another layer of
subselects in the from if that doesn't work.