2

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.

2
  • 2
    Do you have an index on that column?
    – user1822
    Dec 10, 2011 at 14:04
  • Please insert the query and the SHOW CREATE TABLE description of all tables in the query into your question. Dec 11, 2011 at 2:02

3 Answers 3

4

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:

  • Some future topics on indexes not discussed here include:
    • Using UNIQUE Indexe
    • The impact of NULL columns and values on indexes
    • Eliminating filesort by using indexes <<<---- (PLEASE NOTE THIS)
    • The affect of too many indexes
    • Index cardinality
2

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!

1
1

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.