MySQL 5 - DB index date speed times ?

Hi

I have a table with just 3 values: datetime_rx, datum_id, datum_value (for example). The datetime is a time stamp of the time when data is inserted and datum_id the identifier for the datum_value.

The searches are mainly done by date and later by datum_id within the selected days.

The DB works well but when reaching a few million rows, everythig strat to slow down a bit. I then added an index on datetime_rx as most of the date is first filtered by dates. This increased the speed considerably but with 7 million data entries, it takes a while to load first time the index (not to mention index is larger in size than the table itself).

How could I improve the performance of the index? is there anyway that the index can be limited only to the date and ditch the time values? Or would it make more sense to do a new colum with "date" only as 20080502 and then filter using this and re-filtering the results to find the hours I'm looking for?

I know this sounds very simple but I cannot figure out how to improve this...

Thanks

Ed

to create an index just on the date part of a datetime I beleive you can use


Also would a combined index of datetime and ID be possible? It would mean you can have one index for the 2 fields but you would have to call them in your WHERE clauses in the order you have listed in the index.

What does the EXPLAIN say about your query as well?

Thanks for the tip!

I have done the index with limitation to 8 but still have a huge index. The data type is datetime. The problem I got is this:

30 days worth of data are 259200 data (row) entries. Data is updated every 10 seconds and now I get an index that has pretty much the same size. The idea would be to index only date as you mentioned but the lenght of the row still on the thousands.... Shouldn't be just 30?

I suppose the other option is having an extra colum just for date, isn't it?

I will post as soon as I get back to work the EXPLAIN line.

Thanks

Ed