On Fri, Mar 18, 2005 at 09:29:06AM -0000, Alex Stapleton wrote: > We have a ~10million row table but are expecting it to get larger, possibly > by a factor of 10 or more. The rows are quite long and fixed length (just > over 500 bytes.) > > We have an index of (symbol, source, date) on this table and doing queries > like this > > SELECT * FROM article WHERE symbol=12646 AND source = 19 ORDER BY time DESC > LIMIT 1000; > > To get the latest 1000 rows for that symbol and source. > > However this takes quite a while at the best of times, (1-10 seconds.) The > query without the order by and the limit tends to return about 70000 rows > which adds up to about 30MB of data. Once the pages are in the cache they > take around 100ms but this is to be expected. Unfortunately the initial > query required to cache it is unnacceptably long for web application like > ours.
I think the normal approach for this is an index on (symbol,source,time). You may need to change the query to: SELECT * FROM article WHERE symbol=12646 AND source = 19 ORDER BY symbol desc, source DESC, time DESC LIMIT 1000; The EXPLAIN ANALYZE output would also be very helpful... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
pgpZLcFIL16fX.pgp
Description: PGP signature
