How to speed up Group BY?

MySQL & MariaDB Other MySQL® Questions
1

Hello,
I’m a new mysql developer, just started working on performance.
I have large table as 50 mln rec for now ( still growing).

This is a fact table.

I execute the statement in my stored proc :
create temporary table tt as
SELECT SrcUserGroupID, SrcReportObjectID,
DestReportObjectID, FirewallRuleID,
RootCauseID, ServiceID,
Action, SUM(Counter) as Counter
FROM mytable
where DataSourceID=2 and Datex between ‘2009-02-23 20:00:00’ and ‘2009-02-24 19:00:00’
group by SrcUserGroupID,SrcReportObjectID,DestReportObjectID, FirewallRuleID,RootCauseID,ServiceID, Action

I’m not sure how to tune Group By,
I even created an index on all Group By fields and made the order in select the same, but this index is not used.

from status - I’m not using disk temp tables.
what else could I check?

I do have have an index on Datex,DataSourceID.

Please advice!

thank you.
Helen

2

A good place to start for tuning any query is the EXPLAIN command. If you simply prefix your query with EXPLAIN (like EXPLAIN SELECT SrcUserGroupID…) MySQL will give you a high level query execution plan. In particular it will tell you which indexes are being used and roughly how many rows will need to be examined.

Chances are you are probably just reading more rows than necessary. What exactly are the indexes on this table? You can get this by using SHOW CREATE TABLE mytable or SHOW INDEXES FROM mytable.

3

I checked that Explain many times,
I have index on each ID and one compound index for Datex,SourceID . Explain shows me that index but in extra it has " temporary,filesort " - that is becouse of a group by I think.

Why do you think I read more rows then I need?
I have only 1 filter - on Datex and SourceID.
I found a way how to make use of a partition pruning on that table ( my partition function is dayofmonth), that speed up my query 2.5 times, but I still need bettr speed.

do you know what is "filesort " ?
Thank you very much for the fast reply!

----------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±-----------------±----- ±--------±-----±---------±------------------------------ ---------------+
| 1 | SIMPLE | f | ALL | ev_hrly_date_src | NULL | NULL | NULL | 28847656 | Using where; Using temporary; Using filesort |
±—±------------±------±-----±-----------------±----- ±--------±-----±---------±------------------------------ ---------------+