If I frequently ORDER BY o.creationDate - should I mark creationDate with @Index ?

Question

I know that indexes on database are a good way to increase query performance, but how much performance I would gain by creating an index for a creationDate property of a given object that I frequently order by creationDate in my queries? Would this frequently "order by o.creationDate" justify creating an index for the creationDate property?

Answer 1

Yes, if the result set returned from your select is large. Remember that the ordering is (logically) performed on the result set after filtering, but a good optimizer might use an index during filtering for ordering as well. If you are returning a results set with five rows, ordering is trivial. If it contains a million rows, a (B-Tree based) index would definitely help. A hash-index would be of no help, however.

You can get more details with regard to MySQL and indexes for ORDER BY columns from the following article: Database Index Tips

Answer 2

It depends on your read/write ratio as to if it's justified, but will it help? From your usage description yes, an index on creationDate will allow it to run the query without an extra sort operation at the end on it. You can find a full write-up on ORDER BY optimization here.

I'd say if this is a web application or any other high read:write ratio application then yes, it's worth it. Just remember that indexes do have a cost, and inserts or updates on that column will be more expensive when it's added, but when your reads far outweigh the writes, it's usually worth it.