CODEWITHSUNDEEP
mysqlruby-on-railsperformancedatetimeinteger
Jing
Jing

Reputation: 78

Is there a huge performance difference between datetime type and integer type when sorting? Rails 4 and MySQL

I've been using Rails for 2 years and always used :datetime type when creating tables, and these columns were then used for comparison and sorting, such as :start_time and :end_time, etc.

Now I'm working on a new project and I'm thinking of using an additional column to store corresponding timestamps for sorting in the future, which is :integer type.

Is it worth doing to increase performance? The table size will not exceed 1 million rows in the near future, though.

Thanks!

Upvotes: 0

Views: 368

Answers (1)

Rick James
Rick James

Reputation: 142278

No.

Sure, the internal encoding is different. Sure, there is more CPU expended for one versus the other. But the difference is insignificant compared to the effort of fetching and dissecting the rows.

Is the column indexed? Will you be scanning or sorting the entire 1M rows? Optimize at the big end of the spectrum -- minimize the number of rows you need to touch.

Or you could look at the space taken: INT is 4 bytes. In older versions of MySQL, TIMESTAMP was 4 bytes and DATETIME was 8. In recent versions, TIMESTAMP and DATETIME are 5 or more bytes, depending on whether fractional seconds are included.

Don't mess up your application over an optimization you might not need.

Upvotes: 2

Related Questions