Geek Answers Handbook

How to optimize SQLite3 query

I am learning SQLite3 with a book ("Using SQLite") and a Northwind database. I wrote the following code to order customers by the number of customers in my city, and 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-100 ms to start. Is there a standard procedure for optimizing this query or, moreover, queries of its type?

+5
source share
2 answers

In the general case (not only SQLite), it is best to do the calculation for all values ​​(cities) at once, and the union to build 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, as in your case, counting from calculating many times for the same value (city))

+1

. SQLite

EXPLAIN QUERY PLAN statement


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


EXPLAIN statement

.

+7

More articles:


All Articles