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
from products p, categories c
where p.category1 = c.category_id
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
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)