mysql difference between orderby number and timestamp string

377
January 28, 2017, at 10:48 AM

A: publish_time is (int) `2504698546'

select * from my_tbl order by publish_time desc

B: publish_time is (timestamp) `2017-01-01 23:10:23'

select * from my_tbl order by publish_time desc

Which is more accurate? In terms of speed and memory consumption and CPU

Answer 1

First, any difference in speed is likely to be minuscule compared to the overall cost of doing the sort. Second, as with any questions of this sort (pun intended), you should test on your system on your data.

Without an index, there is probably no faster way to sort than a 4-byte integer. That would suggest that the first is faster. There is one additional complication with TIMESTAMP: time zones. I suspect that the comparison of two timestamps is a tiny bit more complicated than for two integers.

All these considerations pretty much disappear if you have an appropriate index. That is the real moral. If you care about performance, don't try to do micro-optimizations such as choosing between two representations of date/time values. Instead, think about things like indexes, partitions, correct data representation, and solid query logic.

READ ALSO
MySQL: How to design a Table

MySQL: How to design a Table

I have a ingredients / meals table setup, where X ingredient combinations can equal Y meals

320
MySQL Calculating Running Total Without Variables

MySQL Calculating Running Total Without Variables

I'm trying to develop a script to update the running total within a table:

331
Jquery autocomplete not showing result, looks like results is hidden somehow

Jquery autocomplete not showing result, looks like results is hidden somehow

Can you please help me to figure out where is the problem is? In a search box, autocomplete jquery function (response, request) not giving me the results, Looks like it grabbing the expected data from the database but result is hidden instead showing...

210
Can a specific character stop data insertion using LOAD DATA LOCAL INFILE?

Can a specific character stop data insertion using LOAD DATA LOCAL INFILE?

I do a daily import of data (approx 62K records) using LOAD DATA LOCAL INFILE from a text file(via cron)

237