Skip to content

Instantly share code, notes, and snippets.

@simonw

simonw/example.sql

Created January 27, 2015 22:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save simonw/aabf1d71c1af2e3d361e to your computer and use it in GitHub Desktop.
Save simonw/aabf1d71c1af2e3d361e to your computer and use it in GitHub Desktop.
Download ZIP
Useful MySQL pattern for quickly finding the most recent 10 rows that match a complex where clause in a giant table
Raw
example.sql
select * from Table FORCE INDEX (primary)
where /* lots of complex where clauses */
order by id desc limit 10;
Raw
explanation.md

Let's say you want to find some examples of records in a database table that match a certain set of criteria (Pages that have a title matching a specific regular expression for example). Even if you have a really expensive WHERE clause, it's still safe to run it on a giant database table provided you only want to see the first X results - the database query will perform a full table scan but it will stop the moment it's returned the correct number of rows. Provided you're confident that you'll hit that number pretty quickly, it's not a problem to run the expensive query against the entire table.

The FORCE INDEX(PRIMARY) clause ensures MySQL doesn't make any dumb decisions about which index to use. We know we want to start at the end of the table and work backwards (or if you drop the "desc" start at the beginning of the table and work forwards).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment