MySQL Database Indexes
Overview
The purpose of this document is to discuss the database index. The concept of Database indexes is nearly the same for all database management systems, As we are using the MySQL database, so syntax and examples we are using here of MySQL. We will discuss the importance of indexes, some problem statement, and how to solve those using index.
Database Index
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table.
Example: Book Index Pages.
Why do we create indexes?
- Boost the query response time.
- We mostly create an index on those columns of a particular table that we use in the WHERE clause of a query.
- If you don’t create an index, the database scans all the rows, filters out the matching rows & returns the result.
- With millions of records, this scan operation may take many seconds & this high response time makes APIs & applications slower & unusable. Let’s understand using a few examples.
How to find slow queries?
MySQL has a feature to log slow SQL queries that takes a long time to respond using config value long_query_time
By default, long_query_time is 10 seconds, but it can be changed.
1- One way is to check the slow queries logs using the slow queries log file, the location of the log file can be found using the command
show global variables like ‘slow_query_log%’ ;
Example: location of the file like
2- By default slow query log is disabled we need to enable it.
3- Another way is we can run SHOW FULL PROCESSLIST to check long-running queries.
How to know on which columns do we need to create indexes?
Now suppose we got the queries that are slow and taking time to respond, how we find the columns on which we need to create indexes.
With EXPLAIN keyword help before the query, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows, also called Query Execution Plans or Explain Plans.
EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.
Let’s understand this using Examples
I have taken examples from my local database.
EXPLAIN SELECT * FROM payments WHERE account_number=’0111212017109';
EXPLAIN SELECT * FROM inquiry WHERE account_number=’0111212017109';
EXPLAIN Output Columns
id- The SELECT identifier
select_type- The SELECT type (Simple if select, not using union or subqueries)
table- Table name
possible_keys- The possible indexes to choose
key- The index actually chosen
key_len- The length of the chosen key
ref- The columns compared to the index
rows- Estimate of rows to be examined
filtered- Percentage of rows filtered by table condition
Extra- Additional information
Get Atul Solanki’s stories in your inbox
Join Medium for free to get updates from this writer.
How to create an index?
Syntax
create index index_name on table_name(column name)
Example: create index inquiry_account_number_idx on inquiry(account_number)
Convention for index Name: TableName_ColumnName_IDX
Note: When we create indexes, it takes time to successfully create an index, if the table has a large set of data. So We need to plan it properly, maybe sometimes we need to take downtime for this.
Review table Indexes
Syntax: show index from tableName;
Example: show index from payment_switch;
Let’s understand each term.
Table- Name of the tables
Non_unique- If the value is 1, the index is not unique, if the value is 0, the index is unique.
Key_name- The name of the index was created. The name of the primary index is always PRIMARY in MySQL
Seq_in_index- The sequence number of the column in the index. If multiple columns are part of the index, the sequence number will be assigned based on how the columns were ordered during the index creation time. The sequence number starts from 1.
Collation- How the column is sorted in the index. A means ascending, D means descending, NULL means not sorted.
Cardinality
- Term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table.
- The lower the cardinality, the more duplicated elements in a column.
- Thus, a column with the lowest possible cardinality would have the same value for every row.
- SQL databases use cardinality to help determine the optimal query plan for a given query.
Sub_part- The index prefix. It is NULL if the entire column is indexed. Otherwise, it shows the number of indexed bytes in case the column is partially indexed. We will define a partial index later.
Packed- Indicates how the key is packed; NULL if it is not.
Null- YES if the column may contain NULL values and blank if it does not.
Index_type- Indicates which indexing data structure is used for this index.
Example: BTREE
Example Of Cardinality
An estimate of the number of unique values in the index. To update this number, run ANALYZE TABLE or (for MyISAM tables) myisamchk -a.
Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.
In mathematical terms, cardinality is the count of values in a set of values. A set can only contain unique values. An example would be the set “A”.
Let the set “A” be: A={1,2,3} — the cardinality of that set is |3|.
Cost of Indexes
- Just as indexes in a book require paper, indexes in databases require storages
- When we add data into the database, it needs to create a new record and relevant indexes.
- Use Indexes but let’s use them wisely.
Example
show table status from the XYZ;
The size is in bytes.
Note- XYZ is the name of the sample database.
Index size in payment table is nearly double that payment switch
Common Mistakes
- Avoid creating indexes on those columns that have lower cardinality i.e. more duplicates records
