Used Indexes |
Tip If possible, write SQL statements in a way that indexes can be used. Background When an index has been defined on one or more columns, there are a couple of situations where the index will not be used. For example, an index will not be used when:
If an index has been defined on more than one column, values are indexed as concatenations of the values in the individual columns. Concatenation is in the order in which the columns are mentioned in the CREATE INDEX command. This means that any WHERE clause that implies a search for the entire indexed string, or for the first column(s) of the index ("front-end searches"), will make the index available. Any WHERE clause that implies a search for the last part of the indexed string but not the front part, will NOT make the index available. Incidentally, this is also why LIKE 'A%' constructs make an index available whereas LIKE '%A' constructs ("back-end search") do not. Examples Rewrite a WHERE clause like: WHERE SUBSTR(name,1,3) = 'AUS' to: WHERE name LIKE 'AUS%' Rewrite a WHERE clause like: WHERE price*2.21026 < 1000 to: WHERE price < 1000/2.21026 Rewrite a WHERE clause like: WHERE ColA||ColB = 'ABC' to: WHERE ColA='A' AND ColB='BC' Rewrite a WHERE clause like: WHERE amount != 0 to: WHERE amount > 0 |