How to optimize an SQLite3 query

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, querie开发者_如何学JAVAs of its type?

---

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.

---

In general (not only SQLite), it's better to do the count for all values (cities) at once, and a join to construct the query:

SELECT ContactName, Phone, Customers.City as originalCity
FROM Customers
JOIN (SELECT city, count(*) cnt
      FROM Customers
      GROUP BY city) Customers_City_Count
ON Customers.city = Customers_City_Count.city
ORDER BY Customers_City_Count.cnt DESC, ContactName ASC

(to prevent, like in your case, the count from being computed many times for the same value (city))