MySQL Forums
Forum List  »  Performance

performance optimization for certain application (most queries = where time > X)
Posted by: firdeiarba
Date: May 16, 2005 11:50AM

Dear MySQL users,

I have a client that frequently inserts data in several MyISAM tables (1-5 rows are inserted in each table every 1-2 seconds; each row contains the time when it was inserted). 5-10 other clients query the tables every couple of seconds and pull the rows inserted since the last query. Virtually all queries look like
select *
from table
where datetime > time_of_previous_query
order by datetime;

So far I've been keeping the tables small by moving the older rows to other tables (to be queried much less often). Is there something else I should look at to try to optimize performance?

In particular, I don't know if MySQL inserts rows in chronological order (I mean, appends each new row or set of rows at the end of the table). If yes, I know for sure that the tables are sorted by time but I guess MySQL doesn't; how can I tell it to use that information when processing queries?

If the above is not true/possible, I'm not sure if I should index the datetime column (the indexes will have to be updated very often -- but I haven't run any benchmarks so far).

Comments and pointers will be appreciated.

Thank you,
dan

Options: ReplyQuote


Subject
Views
Written By
Posted
performance optimization for certain application (most queries = where time > X)
1977
May 16, 2005 11:50AM


Sorry, you can't reply to this topic. It has been closed.

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.