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, queries of its type?

6
задан Heiko Rupp 6 May 2011 в 12:43
поделиться