1

I'm using will_paginate to get the top 10-20 rows from a table, but I've found that the simple query it produces is scanning the entire table. 

sqlite> explain query plan 
SELECT "deals".* FROM "deals" ORDER BY created_at DESC LIMIT 10 OFFSET 0;
0|0|0|SCAN TABLE deals (~1000000 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY

If I was using a WITH clause and indexes, I'm sure it would be different, but this is just displaying the newest posts on the top page of the site. I did find a post or two on here that suggested adding indexes anyway, but I don't see how it can help with the table scan.

sqlite> explain query plan 
SELECT deals.id FROM deals ORDER BY id DESC LIMIT 10 OFFSET 0;
0|0|0|SCAN TABLE deals USING INTEGER PRIMARY KEY (~1000000 rows)

It seems like a common use case, so how is it typically done efficiently?

Jimeux
  • 2,956
  • 1
  • 18
  • 14

1 Answers1

0

The ORDER BY created_at DESC requires the database to search for the largest values in the entire table.

To speed up this search, you would need an index on the created_at column.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks for your reply. I experimented with this, but it's the same as the ID example I posted above. It avoids the `TEMP B-TREE`, but does a table scan: `SCAN TABLE deals USING INDEX index_deals_on_created_at (~1000000 rows)` I went and checked the code I used while following the Rails Tutorial, and it's just as inefficient. Is this a case where we need to rely on caching? It seems wasteful, since the rows are inserted in order of date and incrementing ID. – Jimeux Oct 25 '13 at 07:43
  • I says "SCAN" because this query would have to go through all records without the `LIMIT`. The "USING INDEX" guarantees that it's fast. – CL. Oct 25 '13 at 07:55
  • Okay, if there's no way to lessen the amount of rows scanned, I'll accept your answer. I saw your trick for making subsequent pages more efficient (`WITH value > last_value`), so at least that won't require a table scan. My DB is currently very small, so I guess I'll look into caching before I have any performance issues. – Jimeux Oct 25 '13 at 12:39
  • With an index, it scans at most 10 rows. It says "SCAN" because that is the type of operation to be done, i.e., going through the index entries in order, as opposed to "SEARCH", which would do individual lookups. – CL. Oct 25 '13 at 14:21