Question

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?

     SELECT readings_reading
       INTO lc_initial_read
       FROM readings WITH (INDEX(i_readings_miu_id_read_date))
      WHERE site_id       = li_site_id AND
            readings_miu_id = li_miu_id AND
            readings_date = ld_initial_read_date;

The 3 fields in the where clause are indexed. Would it help to add readings_reading?

Also, what about the following:

     SELECT max(readings_date)
       FROM neptune.readings WITH (INDEX(i_readings_miu_id_read_date))
      WHERE site_id          =  li_site_id AND
            readings_miu_id  =  li_miu_id AND
            readings_date    <= ld_last_read_date AND
            readings_reading <> lc_last_read;

Would it help to have the reading in the index?

Answer 1

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.