How to optimize an SQLite3 query

Question

I'm learning SQLite3 by means of a book ("Using SQLite") and the Northwind database. I have written the following code to order the customers by the number of customers in their city, then alphabetically by their name.

SELECT ContactName, Phone, City as originalCity 
FROM Customers
ORDER BY (
      SELECT count(*) 
      FROM Customers 
      WHERE city=originalCity) 
   DESC, ContactName ASC

It takes about 50-100ms to run. Is there a standard procedure to follow to optimize this query, or more generally, queries of its type?

Answer 1

In the most general case, query optimization starts with reading the query optimizer's execution plan. In SQLite, you just use

EXPLAIN QUERY PLAN statement

In your case,

EXPLAIN QUERY PLAN
SELECT ContactName, Phone, City as originalCity 
FROM Customers
ORDER BY (
      SELECT count(*) 
      FROM Customers 
      WHERE city=originalCity) 
   DESC, ContactName ASC

You might also need to read the output of

EXPLAIN statement

which goes into more low-level detail.