If an SQL statement contains a search condition for key columns, the SQL optimizer can use the following search strategies.
Search Condition |
Search Strategy |
---|---|
Equality Condition for key columns |
The database system accesses those rows for which keys are contained in the inversion list. |
IN Condition for key columns |
The database system accesses those rows for which keys are contained in the inversion lists. |
Range Condition with only one condition (<, ≤, ≥, > ) for one of the two range limits (upper or lower limit) |
The database system accesses those rows for which keys are contained in the inversion lists defined by the range. |
Range condition for which both range limits are specified |
For the selection of the search strategy, it is not important whether the condition was specified by a BETWEEN operator or by two conditions linked by an AND ( ≤ or ≥) for the same column. The database system accesses those rows for which keys are contained in the inversion lists defined by the range. |
Note that depending on the amount of data, the system might use different strategies.
Equality Condition for a Key Column
EXPLAIN SELECT name
FROM hotel.customer
WHERE cno = 7020
Qualification: cno = 7020
Primary key of CUSTOMER table: cno
Indexes via qualified columns: None
Search strategy: EQUAL CONDITION FOR KEY
TABLENAME |
COLUMN_OR_INDEX |
STRATEGY |
PAGECOUNT |
---|---|---|---|
CUSTOMER |
EQUAL CONDITION FOR KEY |
40 |
|
CNO |
(USED KEY COLUMN) |
||
RESULT IS NOT COPIED, COSTVALUE IS |
1 |
Because the primary key was fully qualified in the search condition, the database system accesses the data record directly.
Range Condition for a Key Column
EXPLAIN SELECT zip, name
FROM hotel.city
WHERE zip BETWEEN '10000' AND '20000'
Qualification: BETWEEN '10000' AND '20000'
Primary key of CUSTOMER table: zip
Indexes via qualified columns: None
Search strategy: RANGE CONDITION FOR KEY
TABLENAME |
COLUMN_OR_INDEX |
STRATEGY |
PAGECOUNT |
---|---|---|---|
CITY |
RANGE CONDITION FOR KEY |
11 |
|
ZIP |
(USED KEY COLUMN) |
||
RESULT IS NOT COPIED, COSTVALUE IS |
1 |
Displaying the Search Strategies Used by SQL Statements (EXPLAIN)
You can find additional simple example SQL statements in the SQL Tutorial.