i have a table T with (simplified)schema:
T(Type, Interval, a, b, c, d). all the fields have int type. there is no primary key and no index set on the table and it has about 13 million records (the actual table contains 9 more int fields).
i wrote a query that counts how many distinct d values appear for each triple (a,b,c), with Type = typeValue and Interval between I1 and I2 like this:
SELECT a,b,c, COUNT(d) AS NoOfDValues
SELECT a, b, c, d FROM T
WHERE Interval BETWEEN I1 AND I2 AND Type = typeValue
GROUP BY a,b,c,d
) AS Q1
GROUP BY Q1.a, Q1.b, Q1.c
for the data in my table, this query is executed in about 35 seconds. and i need to run it thousands of time from my app.
how can i optimize both my table (only with indexes) and my query?
update. i tried some indexes as suggested
here, but the best execution time i get for the worst case scenario is 15 seconds. is there a way to make this query run in at most 2 or 3 seconds?
View Complete Post