Results 1 to 5 of 5

Thread: Date index help

  1. #1
    Join Date
    Feb 2008
    Posts
    3

    Unanswered: Date index help

    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

  2. #2
    Join Date
    Feb 2008
    Posts
    3
    Forgot to mention, I use PHP

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

  4. #4
    Join Date
    Jan 2008
    Location
    Kingdom of Sweden
    Posts
    11
    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.

  5. #5
    Join Date
    Feb 2008
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •