Re: Select Last n Rows Matching an Index Condition (and caches)

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(at)svana(dot)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.