The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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?

asked 19 Jan '10, 19:45

Jon%20Greisz's gravatar image

Jon Greisz
accept rate: 0%

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.

permanent link

answered 19 Jan '10, 21:34

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

In this case the tables involved are only inserted to, never updated. So the payment would only be once. Thanks much Glenn.

(19 Jan '10, 23:14) Jon Greisz
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 19 Jan '10, 19:45

question was seen: 588 times

last updated: 19 Jan '10, 21:34