3

Suppose Bob has earned 107 points, Mary 105 points and John also 105 points. These numbers could change and are not unique. Now I want to query and check the order, who is on top and who is on the bottom. Does that column that holds points need to be indexed ?

user1861388
  • 85
  • 6
  • "need" no, but it's probably a good idea – Pekka Apr 25 '16 at 16:39
  • What database system are you using? – Dan Esparza Apr 25 '16 at 16:40
  • Why don't you just create an index and compare performance? we can't do this for you. Here is a hint: http://stackoverflow.com/questions/13441334/when-to-add-an-index-on-a-sql-table-field-mysql – Haytem BrB Apr 25 '16 at 16:41
  • 1
    It might be useful to know why you're thinking that an index wouldn't be useful here - what do you think is true of numeric columns that isn't true of other columns? – Damien_The_Unbeliever Apr 25 '16 at 16:51
  • @Damien_The_Unbeliever Maybe - I thought it could be redundant, just wanted a confirmation. However its a good idea to compare performance, it is exactly what I've read right before, as haytem adviced. – user1861388 Apr 25 '16 at 17:04
  • No one asks this kind of question when their system is running quickly so the answer is always "Yes". – Hogan Apr 25 '16 at 17:34

2 Answers2

4

It depends on the database system used and the size (number of rows) of the table.

If the table is small enough and you're using MSSQL for example, a table scan will be used (the entire table will be read into memory) and an index will be relatively useless.

Generally speaking, yes - the field should be indexed if that is the column you'll be using to select or sort data.

Dan Esparza
  • 28,047
  • 29
  • 99
  • 127
1

As a very general rule, I consider indexing columns that appear in the WHERE or ORDER BY clauses of commonly executed queries.

JSR
  • 6,106
  • 2
  • 20
  • 24