MongoDB Indexing – How many fields should I index

You can store your index as a list of fixed-size offsets into the block containing your key data. For example:

+--------------+
| 3            | number of entries
+--------------+
| 16           | offset of first key data
+--------------+
| 24           | offset of second key data
+--------------+
| 39           | offset of third key data
+--------------+
| key one |
+----------------+
| key number two |
+-----------------------+
| this is the third key |
+-----------------------+

(well, the key data would be sorted in a real example, but you get the idea).

Note that this does not necessarily reflect how index blocks are actually constructed in any database. This is merely an example of how you might organise a block of index data where the key data is of variable length.

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).

• A great partitioning tutorial can be found here: the http://www.arachna.com/roller/spidaman/entry/scaling_rails_with_mysql_table
• You will need to calculate the timestamp ranges for that, but it should not be hard to do.
• Ex: (1346450399 - 1343772000) / 60 / 60 / 24 =~ 31 days. So the max value for the partition holding the data for september (has also 31 days) will be: 1346450399 + ( 31 * 24 * 60 * 60)
• An unix to date calculator can be found here: http://www.onlineconversion.com/unix_time.htm

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)