Why indexing is important for good performance

[databass42]

Great info on indexing

 

[ontopofmalvern]

Many thanks for this, I run a relatively small but complicated database with lots of tables with lots of queries and joins. Typically I need to pull out names from personel tables and join to other records. The primary key in the in personel tables will always be indexed but currently the names are not. If I am using a query with a join to populate a report should I inlcuded the indexed primary key from personel table (even though the report will typically not use it) or should I be indexing the names as well?

Currently if a report does not require the primary key I don't include it in the SELECT part of queries only in the JOIN, so I suspect I'm losing the benefit of the indexed key, is that right? (does it make sense?)

 

[isladogs]

You should index any field used regularly in search criteria as that will significantly speed up the search.
See this article Optimising Queries

 

[CJ_London]

.... and for sorting.

Currently if a report does not require the primary key I don't include it in the SELECT part of queries only in the JOIN, so I suspect I'm losing the benefit of the indexed key, is that right? (does it make sense?)

it's the join that matters in this respect, not what is displayed in a form or report. PK's are not normally displayed anyway since their value is meaningless so you don't need to select it unless you need it for some reason. You might want to index the name for sorting or criteria reasons since this is something the user would typically use to find the employee. However if the number of employees is small, you probably won't notice any real benefit.