Some info, if not a definitive answer
It's been blogged recently
There is a whitepaper too. See the section "Maintaining Statistics in SQL Server 2008" where there are some conditions that sound like affect you. Example:
One limitation of the automatic update logic is that it tracks changes to columns in the statistics, but not changes to columns in the predicate. If there are many changes to the columns used in predicates of filtered statistics, consider using manual updates to keep up with the changes.
At the end there are some settings to check too: what if OFF at the DB level which overrides an ON at the index/stat level?
HTH...
1. Table partitioning
Because of the [AND traffic.time >= 1343772000 AND traffic.time < 1346450399] clause, I imagine that you never delete data from this table, or that the table is currently storing data for multiple months.
The values in the column [time] seem to be unix timestamps (1346450399 = Fri, 31 Aug 2012 21:59:59 GMT)
Partition the table based on time column. That will speed up the data retrieval, as the DB will scan the coresponding partition (much faster then to scan whole table).
2. Rewrite the query
Because the "OR" in your WHERE block, the optimizer will choose not to use the index defined.
Try to split the query in 2 selects, and make an union.
SELECT
traffic.time,
SUM(traffic.bytesin),
SUM(traffic.bytesout)
FROM
traffic
WHERE traffic.type LIKE 'v4_assignment'
AND type_id IN (1,2,3,4)
AND traffic.time >= 1343772000 AND traffic.time <= 1346450399
GROUP BY
traffic.time
UNION
SELECT
traffic.time,
SUM(traffic.bytesin),
SUM(traffic.bytesout)
FROM
traffic
WHERE traffic.type LIKE 'v4_host'
AND type_id IN (5,6,7,8)
AND traffic.time >= 1343772000 AND traffic.time <= 1346450399
GROUP BY
traffic.time
ORDER BY
traffic.time
3. New index based on data cardinality
Based on your explain output, I don't see the index beeing used.
Maybe because the optimizer decides that it will be easier (cheaper) to make a full table scan then to follow the index.
Also, in your current index, the first column has a lower cardinality then the next 2 ones. The first column in any index should be the column with the best (max) cardinality.
Create a new index as:
MYSQL> CREATE INDEX MTIhai_traffic_idx1 ON traffic(time, type, type_id)
Best Answer
Your load profile is the most important factor in answering this question.
If your load is read-intensive, you want indexes to satisfy your heaviest or most frequent queries.
If your load is write-intensive, index carefully. Index to satisfy a seek an UPDATE needs, for example, as well as your one or two most expensive SELECTs.
If your load is an OLAP one, index sparingly since you will be scanning the target tables anyway.
How do you know you have too many indexes?
When you can see that some of them are not used by any queries.
A frequent DELETE, UPDATE or INSERT shows a query plan that involves several expensive index changes (i.e. a nonclustered index insert, update, or delete). Use your judgement to determine if the penalty on those DML statements is worth the gain you are getting from the indexes that have to be updated.