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)

About these ads

3 Comments

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s