I have the a DATETIME key on which I sort the results and the query is running quite slow. Will it run faster if I change it to TIMESTAMP ?
Because I noticed that ordering by numbered keys is done almost instantly.
As pointed out to me in the comments this relates mostly to SQL Server, rather than MySql as the question tags asked for.
On SQL server Timestamp is a special column and is also known as rowversion. It's a global counter, whenever a row is updated the rowversion is increased by 1 and this is recorded against the updated row. It doesn't record a time.
This means it'll change whenever it is updated, which makes it a poor candidate for a key.
If a column is needed that is updated automatically whenever the row is, doesn't need to store the time, but just the order of the updates then it could be used.
Whatever the server, it could benefit the performance slightly, you'd have to measure it and see, but would have next to no impact compared to adding an index.
You can shorten the running type of queries by indexing certain columns.
Since you mentioned sorting DATETIME fields, one of the best ways to bypass sorting of temp table results is to use a covering index which includes the DATETIME as the last column of any given compound index you need.
I know this is entirely possible because in a link about Covering Indexes By Ronald Bradford, he gave a list of future topics he was giving. Here is that list:
datetime consumes 8 bytes whereas timestamp 4 bytes. So the query will be faster since the index size will be smaller. Apart from that timestamp has small range than date-time and hence can be called optimized datatype by default.
But I am not sure to what extend it will be faster. May be 5% or 10% ?
You may find at the end that it is not worth all the efforts of changing the data type!