Query Optimization on time-based data

[Greg Smith]

Hi all,

I am trying to optimize this table called data_input_values:

Field Type Null Key Default Extra
populated timestamp NO MUL CURRENT_TIMESTAMP
data_point_source_id mediumint(9) NO NULL
value mediumint(9) NO NULL
method varchar(6) NO update

I have an index on (populated,data_point_source_id).

The table has about 250k records in it on average (purged daily).

The following SELECT query is running a few times a second. (There are less frequent queries running to pull historical trends, etc)

SELECT `populated` , `data_point_source_id` , `method` , `value`
FROM `data_point_values`
WHERE `data_point_source_id` =1
AND `method` LIKE 'update'
ORDER BY `populated` DESC
LIMIT 1

And an explain on that look like:


id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE data_point_values index NULL populated 7 NULL 2916 Using where

So there are a lot of rows being examained. I have tried separate indexes on populated and data_point_source_id but that seems to be slower.

Is there a better way to index the table? Or a more appropriate DBMS for time-based data? Or changes to my.cnf? The system is xampp with mysql 5.0.51a-log. mysql-nt.exe runs at very high CPU % due to all these select queries running.

Any help/suggestions appreciated.
Thanks.