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 without having such a massive size 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

Forgot to mention, I use PHP

are you, your techies or your ISP worried about space?
does it matter what size the index is, if its required for the process?
how have you defined the datetime_rx, what data type is it?

i doubt the tabnle type will make much of a difference, but if you can it may be wirth using MyIsam as the data engine, unless of course you need the relational integrity required, in which case INNODB is the only choice.

as to whether you can ditch the datetime value I don't know, that surely is up to your application needs. Unless you have a very very good reason I wouldn't store a date value in anything other than a date datatype

You can create the index like this:

CREATE INDEX idx_name ON table_name(datetime_rx(8));

This will create the index using the first 8 charachters.

Thanks a lot for the help!

by0nder:
- That reduced the size of the index. I was using phpmyadmin and it didn't let me set the size of the index, your sql worked! Thanks

healdem:
- Regarding space, yes we were worried because the index was aproaching 100MB size and the server shares resources with other programs. At the rate we are inserting entries, the total volume of data is doubled every month so I was worried about the server running out of memmory to load the index before the query. Correct me if I'm wrong here?

- datetime_rx is "datetime" data type

- Not sure I understood well your comment about the DB type, we are using MyIsam, is INNODB faster?

Thanks

Ed