Performance problems querying large data sets

[Patrick Schiel]

I have problems finding the correct approach to query statistics for a quite large data set.

The simplified table setup is like this:

Items: ~100 million rows
- id
- user_id
- name
- property1
- property2
- ...
- property10


Statistics: ~2 billion rows
- item_id
- date
- value1
- value2
- ...
- value10


I want now build a query based on the items table, that can filter and sort both by the various item properties, and the collected statistics.


A "normal" approach looked like this:

SELECT *
FROM items
LEFT JOIN statistics on items.id=statistics.item_id AND statistics.date="2022-01-01"
WHERE user_id=123
AND items.property3="foo" AND items.property5="bar"
ORDER BY statistics.value1


Now I'm a bit lost how to approach this. I can't create indexes for every combination of filters? And when the filters or sorting are applied on the (left) joined table, nothing seems to work.

How could you handle such a large data set?
Is denormalization maybe a good approach?
And/or putting single indexes on every used column?
Or is that just beyond the limits of what you should do with mysql, and use a different technology?

The DB instance is very big already, over 200GB RAM, so there is not much room to improve hardware side.

The date range for the statistics table is already cut down significantly (only the last few months instead years).

The problem also gets a lot worse when I want to consider date ranges (using SUM on values, GROUP on items and HAVING for filters), but the above example is already enough to result in timeouts.

Thanks for any hints!