Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Do you have an index on that column? –  a_horse_with_no_name Dec 10 '11 at 14:04
    
Please insert the query and the SHOW CREATE TABLE description of all tables in the query into your question. –  RolandoMySQLDBA Dec 11 '11 at 2:02
add comment

3 Answers

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!

share|improve this answer
add comment

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.

share|improve this answer
    
I think you are mistaking SQL-Server's "timestamp": sqlteam.com/article/timestamps-vs-datetime-data-types with MySQL's timestamp: dev.mysql.com/doc/refman/5.1/en/timestamp.html –  ypercube Dec 10 '11 at 22:57
    
Yep I had, thanks for the info. –  webturner Dec 11 '11 at 9:45
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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