Select and create an optimal index for faster data access

Faults caused by SQL problems emerge frequently in a database. Indexing problems are the most frequently occurring SQL problems. Common index problems include no index, implicit conversion, and improper indexing.

When no index is created for a database table referenced by a SQL statement, a full table scan is performed. If the table has a large data volume, the scan task is slow. In this case, database connections are so occupied that the specified maximum number of connections are soon reached. As a result, new requests are denied, and faults emerge.

Implicit conversions may cause an index to fail when the value passed into the conditions in a SQL query is inconsistent with the field data definitions. Here are a few examples of common implicit conversions. The CHAR data type is defined in the table schema for the field referenced in the SQL query, but a numeric field value is passed into the SQL query. Or the case-sensitive collation is defined for fields, but the case-sensitivity definitions for joined table fields may differ in case of multi-table joins. Implicit conversions may cause an index to fail, thereby resulting in the foregoing situation in which the SQL query runs slowly and database connections become drained.

Indexing policies and optimization

Create indexes

1. Create an index on a field that is frequently referenced in queries, but not frequently updated through operations like adding, deleting, or modifying the field data.
2. Use a field directly after “order by” and “group by”, which must be an indexed field.
3. Create up to six indexes in a table.
4. Make sure that the values in an indexed field have a fixed length and are short.
5. Make sure that duplicate values are not too many in an indexed field.
6. Create an index on a highly filterable field.

Index usage notes

1. When the keyword “like” is used, the index may become invalid if the prefix “%” is added.
2. Columns with null values are automatically excluded from candidate columns for indexing, because an index is usually not created on a column containing null values.
3. When the keyword “or” is used, if a field to either side of “or” is not indexed, the index becomes invalid for the other field, even if it is indexed.
4. When the operator “!=” is used, indexes are not used. This is because the indexing efficiency is low and the data range is uncertain in this case. Instead, a full table scan is performed.
5. Do not perform computation in indexed fields.
6. In case of a composite index, the “Leftmost Prefixing” principle must be followed. The first indexed field must be used when you perform a query. Otherwise, the index becomes invalid. Also try to keep the field sequence consistent with the index order.
7. Avoid implicit conversions by keeping the defined data type consistent with the data type passed into the SQL query.