Index Definition and Usage (Magic xpa 3.x)
For best response time RDBMS indexes should be used for most data retrieval. Usually the RDBMS uses one of the indexes when the SQL statement has a WHERE clause on one or more first segments of that index and the requested record order is consistent with that index.
Each of the following examples illustrates which SQL statements use the indexes and when. For these examples, assume there is an index IN1 on fields F1, F3, F5 of table TBL1, and another index IN2 on F3, F4, F5.
Magic xpa issues this next statement when using the first key and ranging on F1 with the same expression for FROM and TO, and on F3 with two different expressions.
Example:
SELECT F1, F2, F3, F4, F5
FROM TBL1
WHERE F1= 1
AND F3= 100
AND F3= 200
ORDER BY F1, F3, F5
Index IN1 will be used for the range on F1 and F3. The order will be achieved automatically by using the index.
Magic xpa issues the next statement when using the first key and ranging on F1 with the same expression for FROM and TO, and on field F2 with two different expressions.
Example:
SELECT F1, F2, F3, F4, F5
FROM TBL1
WHERE F1= 1
AND F2<= ’x’
AND F2>= ’c’
ORDER BY F1, F3, F5
Index IN1 will be used for the range on F1. The RDBMS searches all records with F1= 1. Only those with F2 between c and x will be in the result table. The order will be achieved automatically by using the index.
Magic xpa issues this next statement when using the first key and ranging on F1 with a different expression for FROM and TO, and on field F3 with two different expressions.
Example:
SELECT F1, F2, F3, F4, F5
FROM tbl1
WHERE F1>= 1
AND F1 <= 10
AND F3>= 100
AND F3<= 200
ORDER BY F1, F3, F5
Index IN1 will be used for the range on F1. The RDBMS searches all records with F1 between 1 and 10 and compares them to the range of F3 values. The range on F3 is not done by using the index because the range of the previous segment was not on a single value. The order will be achieved automatically by using the index.
Magic xpa issues this next statement when ranging on F1 with a single expression and on F3 with two different expressions and using the second key.
Example:
SELECT F1, F2, F3, F4, F5
FROM TBL1
WHERE F1= 1
AND F3>= 100
AND F3<= 200
ORDER BY F3, F4, F5
Index IN1 will be used for the range on F1 and F3. The RDBMS orders query results by sorting the result table. The second index cannot be used to supply the requested order because the first index is used for range. Using sort is relatively fast, as only the result table will be sorted and in most cases it will be relatively small.
|
Note:
|
Indexes take up space in the database and are time-consuming when inserting, updating, and deleting records from the database. In some extreme cases indexes can cause poor performance for SELECT statements. For example, when a table’s data consists of less than a block of disk space, which can be several thousand records for a normal table, a SELECT statement does not perform well. When accessing the table through an index the RDBMS actually executes two I/Os; one for the index and one for the data. Executing a full table scan on a single block that was read into memory in a single I/O is faster because memory access is always faster than disk I/O. When all of the columns selected in the query are in the index, the RDBMS will then access only the Index and not the data - this is called a cover index query.
However, in most cases, indexes will enhance the speed of your project.
|
