Do queries make use of more than one index at a time?

开发者_StackOverflow中文版If I have a table with an index each on a different column, does the database ever make use of both indexes when executing a query? Additionally, if I have an index on 4 columns, and an additional index on one other column, could a query against all 5 columns make use of this 2nd index, or would it just be a region scan after matching the first index?

---

If I have a table with an index each on a different column, does the database ever make use of both indexes when executing a query?

If the cost-based query optimizer determines that it's more efficient to use more than one index, yes, it will. If it's more efficient to do a scan (and often it is), then it may not use an index, even if you think it should.

Additionally, if I have an index on 4 columns, and an additional index on one other column, could a query against all 5 columns make use of this 2nd index, or would it just be a region scan after matching the first index?

Again, if the optimizer thinks it's efficient to do so, yes it'll use that other index for the same query. If it determines the cost is higher with the index...it'll ignore it. It all depends on how selective (or rather, how selective the optimizer thinks it is, based off the latest statistics) as to whether it'll use the index. If it's not selective (won't narrow down the results much), it'll likely ignore it.

---

It depends on the optimizer and the query, but optimizers relatively seldom use two separate indexes on a single table in a single query. It is perfectly feasible to construct examples where they could, possibly even should - and some may actually do so. Consider:

• A UNION query where the separate terms have filters on different columns (but a table scan may be as effective)
• A self-join where the separate sides of the self-join have the different filters.

However, be wary of accusing the optimizer of not being efficient - there may still be advantages to resolving the query by other methods.

---

To answer your 'index on 4 columns' questions: it is rather unlikely. In this scenario, it is likely that the 4-column index provides good selectivity and the query is most easily resolved by applying the extra filter condition to the rows retrieved by the index scan. (Note that the answer might be different depending on whether the extra condition is connected to the other by AND (as I assumed) or OR (where using the second index might be useful).

---

It depends upon the queries emitted against those tables, the size of the tables and the selectivity of the data in the columns indexed.

The optimizer uses statistics to determine whether using an index will be beneficial.

---

1.IF I have a table with an index each on a different column, does the database ever make use of both indexes when executing a query?

It certainly can, for example if you have the table

• EMPLOYEE(

  id (index1)

  name

  address

  date (index2) )

and the table

• TASKS(

  id

  employee_id (index3)

  date (index 4)

  category

  description)

If you do the query:

• select employee_id,date,category,description from EMPLOYEE, TASKS where EMPLOYEE.id=employee_id and EMPLOYEE.date=TASKS.date

this will list all the tasks of each employee in each day and user index1 and index2 along with index4 and index3. Which will take much more time if I where lacking either index1 or index2.

2.if I have an index on 4 columns, and an additional index on one other column, could a query against all 5 columns make use of this 2nd index, or would it just be a region scan after matching the first index?

Of course it can be done, but the query should include joins on both the 4 column index and also the single column index.