MySQL Forums
Forum List  »  Optimizer & Parser

Query Optimization on time-based data
Posted by: Greg Smith
Date: August 03, 2010 10:06AM

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

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.

Options: ReplyQuote

Written By
Query Optimization on time-based data
August 03, 2010 10:06AM

Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.