Indexing and Key in Database

Note

• Indexing → care about the distinct value of the column
  • more duplicated value → low performance
• Index affects to IS_NULL operator
  • when this column is not indexed → needs a table full scan to find null values.

Why we need indexes for Database tables

Benefits

• Speed up searching.
• Indexing helps in faster sorting and grouping of records.

Drawbacks

• Additional disk space
  • The clustered index doesn’t take any extra space as it stores the physical order of the table records in the DB.
  • Non-Clustered Index needs extra disk space.
• Slower data modification
  • update record in the clustered index

Overview

• The index is nothing but a data structure that store the values for a specific column in a table (an index is created on a column table).
• Improve the speed of data retrieval operations.
• With DML operations, indices are updated, so write operations are quite costly with indexes.
  • The more indices you have, the greater the cost.
  • Indexes are used to make READ operations faster.
  • So if you have a system that is written-heavy but not read-heavy, think hard about whether you need an index or not.
• Cardinality is IMPORTANT
  • Cardinality means the number of distinct values in a column.
  • If you create an index in a column that has low cardinality, that’s not going to be beneficial since the index should reduce search space. Low cardinality does not significantly reduce search space.

Clustered and Non-Clustered index

A clustered index is a table where the data for the rows are stored

Each table has only one clustered-index - that stores row data

• When we define PK → InnoDB use it as the clustered index
• If we don’t define a PK → It will use the first UNIQUE index in this table
• If a table has no PK or suitable UNIQUE index → It will generate a hidden clustered index: GEN_CLUST_INDEX.

Each record in a secondary index contains the PK columns for the row as well as the columns specified for the secondary index.
All InnoDB indexes are B-trees where the index records are stored in the leaf pages of the tree.

• The Default size of an index page is 16KB MySQL::InnoDB Page Size The MEMORY storage engine (known as HEAP) supports both HASH and BTREE index → creates special purpose tables with contents that are stored in memory. In this engine, there
• HASH for equality operator (only available on MEMORY engine)
• BTREE for range operator (both in MEMORY and InnoDB)