0

I have a table (Table1) with 3 columns. I have created an index on the third column. I have selected the first and the second column in my SELECT statement and these same two columns are used in the WHERE clause of my query.

Will the index be useful or not?

Wolfgang Kais
  • 4,010
  • 2
  • 10
  • 17
Ashish Karnavat
  • 96
  • 1
  • 1
  • 10
  • 2
    Probably not, but why not just run the query asking for the execution plan and see if its used? – Damien_The_Unbeliever Oct 10 '18 at 12:41
  • If you are not including an indexed column as a filter, the index will not be used. – FDavidov Oct 10 '18 at 12:41
  • @Damien_The_Unbeliever, I would not say "probably" but "certainly". – FDavidov Oct 10 '18 at 12:42
  • If you are not referencing the column in the index, then it will not be used for the query (except perhaps for a `select count(*)` query). – Gordon Linoff Oct 10 '18 at 12:46
  • 1
    If the appendix of a telephone book contained an ordered list of the telephone numbers, would this be useful when looking for a list of all entries with a name of "Smith, James"? – Wolfgang Kais Oct 10 '18 at 13:01
  • @FDavidov - "you're not guaranteed that it won't use this index but it would be a remarkable find" :-) – Damien_The_Unbeliever Oct 10 '18 at 13:01
  • @Damien_The_Unbeliever, if you are selecting values from not indexed columns and the conditions (`WHERE`) does not reference any indexed column, and still the DBMS does take a look into the indices, someone would need to review his/her doings :-). – FDavidov Oct 10 '18 at 13:04
  • @GordonLinoff, the `SELECT COUNT(*)` with no **INDEXED** conditions would not look into the indices. And if there are no conditions at all, the number of records in the table are already "cooked" into the internal DB tables (well, at least the DBs I know). – FDavidov Oct 10 '18 at 13:07
  • @FDavidov . . . Interesting that you say that. Most databases that I know execute a counting query for `select count(*)`. Some will optimize such a query to use the smallest available index. I'm pretty sure SQL Server does that, but not 100% sure. – Gordon Linoff Oct 10 '18 at 13:08
  • @GordonLinoff, well, I don't have at hand a very large table (the one I have has about 1M records only). The SELECT COUNT(*) yields a result within few milliseconds. Not sure if this supports your claim or mine. – FDavidov Oct 10 '18 at 13:21
  • In short: No, index on 'unused' column is useless – DDS Oct 10 '18 at 13:54
  • @FDavidov . . . When I see `COUNT(*)` in a query, I see some sort of scanning operation. You may be interested in: https://www.mssqltips.com/sqlservertip/4460/sql-server-count-function-performance-comparison/, https://stackoverflow.com/questions/11130448/sql-count-performance, and https://itknowledgeexchange.techtarget.com/sql-server/why-is-my-select-count-running-so-slow/. – Gordon Linoff Oct 10 '18 at 15:14
  • @GordonLinoff, thanks for the interesting references. I do recall that in Oracle, for instance, you can query the DB to get a list of tables with all their details, including the number of rows. The response is immediate, which means that Oracle book-keeps the changing information of each and every table, including of course the rows count. I do recall also that `SELECT COUNT(*)` would return the number in just a fraction of a second, and this in spite of the fact the table had hundreds of million records. Too much for scan in a fraction of a second. And yet, I might be wrong of course... – FDavidov Oct 11 '18 at 16:45

1 Answers1

0
  • If you are not select your indexing column in selection or filter it will not work
  • Because when you apply indexing it keeps data in sorted order
  • So to get the advantage of indexing you have use that column in your query
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63