Analyzing SQL queries for performance

Here I have two queries they do the same thing but one is 200 times faster than the other.

The point of this post is to show how to analyze a given query, so please do not be distracted by what my queries actually produce.

Query 1:
with t as (select * from trx_lookup where trx_unid > 1000 order by trx_unid desc)
select * from t limit 10 offset (select count(*) from t)-10;

Query 2:
with t as (select * from trx_lookup where trx_unid > 1000),
u as (select * from t  order by trx_unid asc limit 10)
select * from u order by trx_unid desc;

At first my intuition suggested the first query should be faster because it does not require sorting twice. I also (wrongly) thought its row count could be trivially computed (read off a cache perhaps?)

But the facts suggested otherwise:

Query 1: Run Time: real 0.156 user 0.051496 sys 0.014727

Query 2: Run Time: real 0.000 user 0.000228 sys 0.000064

Looking at the query plan tells us why one query is faster. To see the query plan, just put the keywords “explain query plan” in front of the query and run it:

Query plan for Query 1:
0|0|0|EXECUTE SCALAR SUBQUERY 1
2|0|0|SEARCH TABLE trx_lookup USING PRIMARY KEY (trx_unid>?)
1|0|0|SCAN SUBQUERY 2
0|0|0|SEARCH TABLE trx_lookup USING PRIMARY KEY (trx_unid>?)

Query plan for Query 2:
1|0|0|SEARCH TABLE trx_lookup USING PRIMARY KEY (trx_unid>?)
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR ORDER BY

Query 1 reads the table twice (presumably once for the count and once for the data)

Query 2 only reads the table once. In addition, Sqlite is smart enough to build a b-tree to speed up sorting for the second order by.

What have I learned from this? The untrained intuition often leads one astray. Check with the facts!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s