RDBS when to use complex indexes for queries and when is it easy to use?
Suppose I have a table in my DB schema called TEST with fields (id, name, address, phone, comments). Now I know that I am going to do a large set of different queries on this table, so my question is when and why I will create indexes such as ID_NAME_INDX (index for id and name) and when is it more efficient to create separate index for id and index for the name field (when I mean for what type of request)?
a source to share
The overall goal would be to "cover" all columns, so the query should only use the index.
-- An index on Name including ID would be ideal
SELECT
[id]
FROM
TEST
WHERE
[name] = 'bob'
Let's say you want a name and an index, but there are separate indexes. You will get the bookmark search result from the index in PK to get the other columns (assuming it doesn't just scan PK).
Edit, after 1st comment:
select * from test where id='id1' and name='Name1'
SELECT * for this query, but softens any index, so PK will be used. If you have:
select address from test where id='id1' and name='Name1'
then the index on the identifier, including the address, will "cover" it.
The use of "OR" creates difficulties for any strategy. but
select address from test where id='id1' and name='Name1'
will still use the id "Id, name including address", most likely, but scan it rather than search
Read this: Execution Plan Basics
a source to share
I'm not sure if your example explains the real question you are asking. You are saying that if you have to have an index by id and index by name, as opposed to index on both id and name. Thing is, I think ID is your primary key, so you are unlikely to search by ID AND name.
However, in terms of a table with two IDs that you would like to search on one of them, or together with three indexes, one on each ID and one together will be the fastest. If you have two indexes, then both indexes will need to be searched to find the record you are looking for. However, if you have one index covering both IDs, then only that index will need to be searched.
As with all indexes, however, as you add them, your database grows in size and you will experience a decrease in insert / update performance. You should always weigh your profit / loss.
Add indexes to obvious candidates, add indexes to "possibly" as needed. Continue monitoring your database performance and running query parsers to see how any performance gains can be made over time.
a source to share
Most database programs have some kind of tool to debug your queries. They can usually tell you which indexes are considered by the server and which it was using. This functionality is usually referred to as explain or something similar.
Typically, you should create indexes on columns that are used in a where clause or concatenated.
a source to share