SQLITE: Join And Group By vs Group By And Join

A while back I wrote about MySQL count distinct performance issue. Today I am going to discuss about a performance issue I faced with SQLITE.

First the use case. I am experimenting a bit with online product catalogs. So, I have a products table and a categories table. I wanted to get the list of categories grouped providing the count of the items in each category that matches the search criteria. So, I wrote the query as

select c.name,count(1)
from products p, categories c
where p.category1 = c.category_id
and ...
group by c.name
order 2 desc, 1 collate nocase;

The idea is to sort them such that categories with higher product count appear first and if there are two categories with the same product count, they are displayed in alphabetical order (case insensitive).

This query took a lot of time. So, I changed it to

select c.name,g.*
from categories c
,(select category1,count(1) from products where ... group by c.name)
where c.category_id = g.category1
order by 2 desc, 1 collate nocase;

This worked pretty fast. BTW, in the first case, the CPU also maxed out to 90% and above. The later case is much better.

BTW, this is using SQLITE3 (3.3.12)


Filed under performance tuning, SQL Tuning, SQLITE

3 responses to “SQLITE: Join And Group By vs Group By And Join

  1. Pingback: SQLite: Some More SQL Tuning « poeticcode

  2. Pingback: Can we hibernate, seriously? « poeticcode

  3. alex

    Thanks, your tip helped me!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s