Say I have a table with the columns category_id, sub_category_id, time.

I want to be able to do various aggregate queries like SELECT COUNT(*) FROM table WHERE category_id IN (1, 2) AND sub_category_id IN (3, 4) GROUP BY category_id, subcategory_id, DATE_PART('day', time - '2000-01-01 00:00:00'::timestamp).

What would be the best way to store and retrieve data so that such queries are efficient?

New contributor
Johnny is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • What alternative "ways to store data" do you have in mind? – mustaccio Feb 13 at 14:17
  • For example, if I just had one column to aggregate by I could conceivably go for a scheme where that column was stored sorted so scans would be more efficient. – Johnny Feb 13 at 20:11
  • It might be helpful if the table were rarely updated and this were the only type of query you'd ever use against it. DBMS documentation usually provides advice on how to tune performance; you may want to check out documentation for yours (you tagged your question with MySQL but used Postgres syntax in your sample query, so I'm not sure which one you're interested in). – mustaccio Feb 13 at 20:36
  • It sounds like you may want to look into OLAP solutions for MySQL. That link is a blog post from Percona, and while it's on the older side, it will at least get you started on what options are out there. – John Eisbrener Feb 14 at 14:44

Browse other questions tagged or ask your own question.