loading...

Understanding MySQL Index Basics

viralpatelblog profile image Viral Patel ・2 min read

An index is an on-disk or in-memory structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. For on-disk indexes, these keys are stored in a structure (B-tree) that enabled the SQL server to find the row or rows associated with the key values quickly and efficiently.

An index stores data logically organized as a table with rows on columns, and physically stored in a row-wise data format called row-store, or stored in a column-wise data format called column-store.

The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead. Wide indexes, on the other hand, cover more queries. You may have to experiment with several different designs before finding the most efficient index. Indexes can be added, modified and dropped without affecting the database schema or application design. Therefore, you should not hesitate to experiment with different indexes.

row-store has been the traditional way to store relational table data.

Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.

The larger the table, the more these costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

Here is a sample command to add an index to the existing table
CREATE INDEX user_name_index ON user (name);

if using b-tree,

CREATE INDEX user_name_index ON user (name) USING BTREE;

This blog was originally published on my personal blog Viral Patel

Discussion

markdown guide
 

Is there a tool to look at the efficiency of a MySQL query?
Something like SQL Server Execution Plans?
This would really help to let you know which queries need an index and how well the index helps.

 

Katie, you are correct. One of the most commonly used tools is the EXPLAIN command which shows you the execution plans of the query. Read this guideon how to use Explain command to optimize your query.

 

Thanks Viral. It's not graphical like SQL Server, and I think it will take some time to weed through the output and see what's going on. I guess it just takes some time to learn. One more challenge for me!

Oops. I just looked around in MySQL Workbench and found a graphical output for EXPLAIN. So much better now 😊

I see some Non-Unique Key Lookups that are taking all the execution time.