If a query is only pulling back one value from a table using 3 columns in an index, does adding it to the index improve performance by not pulling back data rows?
The 3 fields in the where clause are indexed. Would it help to add readings_reading?
Also, what about the following:
Would it help to have the reading in the index?
asked 19 Jan '10, 19:45
As usual, the answer is "it depends".
Version 11 SQL Anywhere servers support index-only retrieval; if all of the attributes required in the computation are covered by the index, and an indexed retrieval is estimated to be less costly than other access paths, then the server will use the index (and solely the index) to retrieve the necessary values to compute the result.
Both queries above have the potential to be satisfied by index-only retrieval - it doesn't matter that the first query references an attribute in the SELECT list, and the second uses the MAX aggregate function.
The tradeoff to adding a column to an existing index is whether or not your workload as a whole costs less to execute. Adding a column may make index maintenance more expensive, not only because the index is larger but also because your application may update the newly-added column more often than other columns that are already indexed.
SQL Anywhere's index consultant can determine for you whether or not an index can be advantageous with your specific workload.
answered 19 Jan '10, 21:34