samedi 20 décembre 2014

Optimal Table Index for hourly data


I have a table that contains ~40,000,000 records and consisting of the following fields:



idName - foreign key relationship to another table

Date

Hour

Type (two possible types)

Value


Currently I have the table indexed on idName, Date, Hour. The table is typically queried by Date range and joined to other tables on Date and Hour; along with the idName to the Names table.


Can someone tell me if there is a more optimum index? It takes >3 min to run the following select query on this table:



select Type, Max(Date) as MaxDt, count(idName) as RecCt from BigTable group by Type


Thank you in advance for your thoughts.





Aucun commentaire:

Enregistrer un commentaire