Rod Taylor <[EMAIL PROTECTED]> wrote: > On Thu, 2004-04-22 at 07:02, Michiel Meeuwissen wrote: > > Rod Taylor <[EMAIL PROTECTED]> 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.
Ok, I can get it working: select number,url from ( select number,url from (select number,url from only mm_mediasources order by number desc limit 20) as A union select number,url from (select number,url from mm_audiosources order by number desc limit 20) as B union select number,url from (select number,url from mm_videosources order by number desc limit 20) as C ) as TAB order by number desc limit 20; This indeeds performs good (about 10000 times faster then select number,url from mm_mediasources order by number desc limit 20) . But hardly beautiful, and quite useless too because of course I am now going to want to use an offset (limit 20 offset 20, you see..), which seems more or less impossible in this way, isn't it. select number,url from ( select number,url from (select number,url from only mm_mediasources order by number desc limit 100020) as A union select number,url from (select number,url from mm_audiosources order by number desc limit 100020) as B union select number,url from (select number,url from mm_videosources order by number desc limit 100020) as C ) as TAB order by number desc limit 20 offset 100000; This would be it, I think, but this performs, expectedly, quit bad again, though still 5 times faster then select url,number from mm_mediasources order by number desc limit 20 offset 100000; I'm thinking of dropping inheritance all together and using foreign keys or so for the extra fields, to simulate inheritance. That might perhaps work a whole lot better? Thanks anyway, -- Michiel Meeuwissen Mediapark C101 Hilversum +31 (0)35 6772979 nl_NL eo_XX en_US mihxil' [] () ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster