RDBS when to use complex indexes for queries and when use simple?

Question

Suppose I have table in my DB schema called TEST with fields (id, name, address, phone, comments). Now, I know that I'm going to perform a large set of different queries for that table, therefore my question is next, when and why I shall create indexes like ID_NAME_INDX (index for id and name) and when it's more efficient to create separately index for id and index for name field(by when I mean for what type of query)?

Answer 1

The general aim would be to "cover" all columns so the query only has to use the index.

-- An index on Name including ID would be ideal
SELECT
    [id]
FROM
    TEST
WHERE
    [name] = 'bob'

Say you need name and indx but have separate indexes. You'll end up with a bookmark lookup from the index to the PK to get the other columns (assuming it doesn't just scan the PK)

Edit, after 1st comment:

select * from test where id='id1' and name='Name1'

For this query, the SELECT * but mitigates against any index so the PK would be used. If you had:

select address from test where id='id1' and name='Name1'

then an index on ID, name including address would "cover" it.

Using "OR" creates difficulties for any strategy. However,

select address from test where id='id1' and name='Name1'

would still use the "ID, name including address" inex most likely but scan it rather that seek

Read this: Execution Plan Basics

Answer 2

Most database software include some sort of a tool to debug your queries. These can usually tell you which indexes the server considered and which it ended up using. This functionality is usually called explain or something similar.

Usually you should create indexes for columns that are used in the where clause or joins.

Answer 3

I'm not sure your example explains the actual question you're asking. You're saying if you should have an index on ID and and index on Name, as opposed to an index on both ID and Name. The thing is, I guess that ID is your primary key and so you're not likely to do a search on ID AND Name.

However, in the terms of a table with two ID's of which you would want to search on either one, or both together then having three indexes, one on each of the ID's and one combined will be the fastest. If you have two indexes then to find the record you're looking for both indexes will need to be searched. However, if you have one index covering both ID's then only that index will need to be searched.

As with all indexes though, as you add them, your database increases in size and you will get a reduction on insert / update performance. You always need to weigh up the gains / losses.

Add indexes to the absolutely obvious candidates, add indexes to the "maybe" ones as the need arises. Continue to monitor your database performance and run query analysers to see where any performance gains can be made over time.