With the query given, the optimiser can immediately figure out to get the latest record from the index and scan back through the index. If the index has included columns, it could scan the data straight off the index. Without the index you need to scan the entire table, sort it in memory, and then read off the top columns. If you were doing a top X query it would be more markedly faster by fetching less data from disk. But I think that query is getting all the records, but still it will be quicker by avoiding the in memory sort.
interesting, thanks for the insight. I haven't touched DB setup in many years, and even then was novice. The best person I knew told me to index if a column wasn't unique, but also wasn't something with only two or three choices. Sounds like I have more reading to do...
I'm certainly not an expert - I did a great DB course about 15 years ago and then used the skills every now and then since. I might not be up on the latest. And I am more of a SQL Server person. BUT... the main thing I learn is view actual execution plans, and see what is actually happening before adding indexes (unless it's an 'obvious' index).
> The best person I knew told me to index if a column wasn't unique, but also wasn't something with only two or three choices.
Yeah I think this is too broad advice, and you need to understand what you want to achieve. Mentally, choosing indexes is like choosing whether to use a hashtable vs. for loop vs. binary tree etc in an algorithm in code. There is not golden rule or "always use a hashtable if there are 100 entries" type thing. You just need to figure it out on a case by case basis. And usually there are only 2-3 tables in your DB worth a lot of effort in figuring it out!
So, in fairness I didn't dig through the code to see the column, but assumed some granularity like milliseconds? It was a genuine question on whether it would speed up the query at all if indexed since it is generally grabbing everything then sorting.
