I think I have quite simple question, but I couldn't find the answer anywhere. I'm using MySQL and I have a simple table:
Some rules of the table:
- timestamp is always the time dividable by 15 minutes.
- for each timestamp there is about 5000 rows,
- there are > 5k groups,
- there are > 5k new rows each 15 minutes, which makes about 500k new rows a day,
- currently I have one index ON (timestamp, groupId).
I want to query that table in two ways:
SELECT timestamp, SUM(costA), SUM(costB), SUM(costC) FROM table WHERE timestamp BETWEEN :date1 and :date2 AND groupId IN (:idList) GROUP BY timestamp
SELECT groupId, SUM(costA), SUM(costB), SUM(costC) FROM table WHERE timestamp BETWEEN :date1 and :date2 AND groupId IN (:idList) GROUP BY groupId
To do so - I created an index ON two columns (timestamp, groupId), but when I try to run the query on long date range (for example a month - which makes a sum of 15M rows), the MySQL is either very slow (takes minutes to execute it) or the timeout occurs and select cannot be executed.
I just wonder what I could do to make it really fast, like respond in less than 1 second for a month set. I don't know if the index I have is correct, but when I use "DESCRIBE" it looks like it's using correct index or maybe I should add more memory to the MySQL server?