I have a query that groups results in the order of 300,000 rows.
What I've tried is to create an index with the group by columns. When I run the query without the SUM function, the query seems to run pretty fast, but when I add a column with SUM, then the response time increases a lot. For example no SUM query response in less than 1s (paginated), SUM query response in 20s.
Supposing there's a index on a,b,c,d,e columns and there are 300,000 records in TableA :
response in less than 1s (in SQL developer, which shows first 50 rows):
SELECT a,b,c,d FROM tableA GROUP BY a,b,c,d
response in 18s (in SQL developer, which shows first 50 rows):
SELECT a,b,c,d,SUM(e) FROM tableA GROUP BY a,b,c,d
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `tableA` ADD INDEX `tablea_idx_a_b_c_d` (`a`,`b`,`c`,`d`);
SELECT tableA.a, tableA.b, tableA.c, tableA.d FROM tableA GROUP BY tableA.a, tableA.b, tableA.c, tableA.d ORDER BY NULL