Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2007
    Posts
    23

    Unanswered: Datetime Indexing Problem?

    Hi guys,

    I have a table with over a million rows in it. Lately I've noticed some slowness even on some simple queries. Maybe I'm not understanding something obvious about indexes so I'm hoping some one can enlighten me.

    Each record has a datetime field to keep up with when that record was created. If I run the following query:

    select * from table where datetime > '2012-01-01'

    It won't use the index that I have placed on the datetime field.

    If I run this next query, however:

    select 1 from table where datetime > '2012-01-01'

    It uses the index fine. Does the index only apply to itself? If I want to reference multiple columns, do I need to create an index on multiple fields?

    This is where things get weird. If I run this query:

    select * from table where datetime > '2012-06-01'

    It uses the index fine. Is whether or not it uses the index some how dependent on the number of rows returned? Why does the index work when selecting just the last couple of months, but not since the beginning of the year?

    Any help would be appreciated.

  2. #2
    Join Date
    Dec 2007
    Posts
    23
    Another bit of useful information is that the table's storage engine is InnoDB.

  3. #3
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    How do you know when the index is used and when it is not used?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by papadi View Post
    How do you know when the index is used and when it is not used?
    Explain or describe tells you how the SQL engine processes the query
    MySQL :: MySQL 5.0 Reference Manual :: 8.2.1 Optimizing Queries with EXPLAIN
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2007
    Posts
    23
    Quote Originally Posted by healdem View Post
    Explain or describe tells you how the SQL engine processes the query
    MySQL :: MySQL 5.0 Reference Manual :: 8.2.1 Optimizing Queries with EXPLAIN
    This is exactly right.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im wondering if you should be expressly converting the date literal to a datetime

    it could be a quirk of the data that is in the db. ie the parser may decide its quicker to do a tablescan than use an index if the where clause is going to return most if not all rows

    another thought is that perhaps the indexes need rebuilding
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2007
    Posts
    23
    Quote Originally Posted by healdem View Post
    Im wondering if you should be expressly converting the date literal to a datetime

    it could be a quirk of the data that is in the db. ie the parser may decide its quicker to do a tablescan than use an index if the where clause is going to return most if not all rows

    another thought is that perhaps the indexes need rebuilding
    I wondered the same thing about converting and I've tried it and it doesn't make a difference. Furthermore MySQL documentation very plainly states that the way to do datetime comparisons to a static date is to compare to a string in the format of 'YYYY-mm-dd hh:mm:ss'.

    Where clause returns as little as 20% of the rows when the index isn't being utilized.

    I might try rebuilding indexes.. on a million plus row table though that is no small feat...

  8. #8
    Join Date
    Dec 2007
    Posts
    23
    I ran an optimize table query on it, which with innodb tables essentially rebuilds the table. It took about 20 minutes. Looks to be working much better now.

  9. #9
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Explain or describe tells you how the SQL engine processes the query
    Yup. I probably misunderstood the original post My thinking was that it would be tough (impossible?) to determine "after the run".

    As the query and the data content and possibly the db definition might have changed along the way, the EXPLAIN might show unrelated results.

    Good to hear the time problem has been improved.

Posting Permissions

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