How to optimize an SQLite3 query
In the most general case, query optimization starts with reading the query optimizer's execution plan. In SQLite, you just use
EXPLAIN QUERY PLAN statementIn your case,
EXPLAIN QUERY PLANSELECT ContactName, Phone, City as originalCity FROM CustomersORDER BY ( SELECT count(*) FROM Customers WHERE city=originalCity) DESC, ContactName ASCYou might also need to read the output of
EXPLAIN statementwhich 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))