Skip to content

Index Guideline

Index Rules

Unique index should be used if business logic is applicable. [Mandatory]

💡 Negative impact of unique indices on insert efficiency is neglectable, but it improves query speed significantly. Additionally, even if complete check is done at the application layer, as per Murphy’s Law, dirty data might still be produced, as long as there is no unique index.

Columns to be joined must be with absolutely same data types. And make sure that columns to be joined are indexed. [Mandatory]

💡 Prevents the implicit conversion caused by different field types, which will cause the index to fail.

Index length must be specified when adding index on varchar columns. The index length should be set according to the distribution of data. [Mandatory]

💡 Normally for char columns, an index with the length of 20 can distinguish more than 90% data, which is calculated by count(distinct left(column_name, index_length)) / count(*).

LIKE '%…' or LIKE '%…%' are not allowed when searching with pagination. Search engine can be used if it is really needed. [Mandatory]

💡 Index files have B-Tree’s left most prefix matching characteristic. Index cannot be applied if left prefix value is not determined.

💡 Indexing and SQL performance should be considered even if only 2 tables are joined.

💡 Make use of the index order when using ORDER BY clauses to avoid index to fail.
👍 where a=? and b=? order by c; Index is: a_b_c
👎 The index order will not take effect if the query condition contains a range, e.g. where a>10 order by b; Index a_b cannot be activated.

💡 The covering index helps us to avoid the round trip to the table to satisfy the request, since all of the columns requested exist in the index itself. This greatly reduces logical and physical reads, hence boosting performance.

💡 Instead of bypassing offset rows, SQL (for MySQL, PostgresSQL, OracleSQL) retrieves totally offset+N rows, then drops off offset rows and returns N rows. It is very inefficient when offset is very big. The solution is either limiting the number of pages to be returned, or rewriting SQL statement when page number exceeds a predefined threshold.
👍 Firstly locate the required id range quickly, then join: 

select a.* 
from table1 a, (select id from table1 where some_condition LIMIT 100000, 20) b 
where a.id=b.id;

💡 When equal and non-equal check both exist in query conditions, put the column in equal condition first when adding an index. For example, where a>? and b=?, b should be put as the 1st column of the index, even if column a is more discriminative.
👍 For the sub-clause where a=? and b=?, if data of column a is nearly unique, adding index idx_a is enough.

💡 Pay attention to these scan types (MySQL: type=ALL, index; PostgresSQL: Seq Scan; OracleSQL: TABLE ACCESS FULL, INDEX FULL SCAN;) in EXPLAIN result because it is very slow for the database index file, whose performance equals or nearly equals to an all-table scan.
👎

Index & Table Access MySQL (EXPLAIN) PostgresSQL (EXPLAIN) OracleSQL (EXPLAIN) Description
Full Table Scan Type: ALL Seq Scan TABLE ACCESS FULL Reads the entire table—all rows and columns—as stored on the disk. Besides high IO rates, a full table scan must inspect all table rows so it can also consume a considerable amount of CPU time.
Full Index Scan Type: index / INDEX FULL SCAN Reads the entire index—all rows—in index order. Depending on various system statistics, the database might perform this operation if it needs all rows in index order

Avoid listed below MISUNDERSTANDINGS when adding index. [For Reference]

  1. Each query needs one index.
  2. The index consumes story space and degrades update, insert operations significantly.
  3. The unique index should all be achieved from application layer by "check and insert".