•  

Announcement

Announcement Module
Collapse
No announcement yet.

ORDER BY float is slower than date

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • ORDER BY float is slower than date

    #1
    I have a query that is identical except for the method of sorting. Here are the differences.

    ORDER BY score
    ORDER BY entry_date DESC

    I created two indexes and verified that they are being used and no filesort occurs, but the entry_date query is ~20X faster than score.

    score is a float and entry_date is datetime. I didn't specify a size for the float, but in looking at the data they all have either a 0 or 1 and 6 decimal places.

    Is there a different datatype that I should be using?
    Tags: None

  • #2
    Could it have anything to do with the order in which the data is sotred on your disk? For MyISAM this is the insertion order, so the table is sorted on entry_date already. Fully random i/o is way slower than (almost) sequential look-ups.

    Comment


    • #3
      @gmouse

      I tried the reverse ordering, but didn't think about the initial ordering for date being closer to optimal. The data definitely falls that way. If that is the reason there may not be anything more that I can do.

      Does anyone know if DECIMAL is supposed to be faster than FLOAT?

      Comment

      Working...
      X