How to use Indexes in MySQL in Linux

Introduction to Indexes

Indexes are an important part of optimizing MySQL databases. An index allows the database to find and retrieve data from tables much faster by storing a sorted list of values that point back to the full records.

Without an index, MySQL must scan through every row of a table to find relevant data. This can become very slow as tables grow larger. Adding proper indexes allows MySQL to navigate the table data much more efficiently.

Some key benefits of using indexes include:

• Faster lookups for specific rows and values
• Improved performance of various queries, especially JOINs and WHERE clauses
• Ability to optimize data retrieval across large database tables
• Faster sorting and grouping operations on indexed columns

MySQL supports several index types to improve queries in different ways:

• NORMAL – Basic index on single or multiple columns
• UNIQUE – Ensures uniqueness of indexed values
• FULLTEXT – For optimizing text-based searches
• SPATIAL – For spatial data like locations

This guide will provide examples of creating and using these different MySQL index types to improve database performance.

Connecting to MySQL and Setting up a Sample Database

Before demonstrating indexes, we need a MySQL database with some sample data. Here are the steps to connect to MySQL and create a simple “contacts” database:

1. Connect to the MySQL server from the command line with the mysql client:

$ mysql -u root -p

2. When prompted, enter the password for the root MySQL user. You should now be at the MySQL monitor.
3. Create a new database called contacts:

mysql> CREATE DATABASE contacts;

4. Switch to the new contacts database:

mysql> USE contacts;

5. Create a contacts table with some sample data:

mysql> CREATE TABLE contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  phone VARCHAR(20)
);
mysql> INSERT INTO contacts (first_name, last_name, email, phone) 
VALUES
  ('John', 'Doe', '[email protected]', '555-555-5555'),
  ('Jane', 'Doe', '[email protected]', '555-555-5556'),
  ('Bob', 'Smith', '[email protected]', '555-555-5557');

This creates a simple table to store contact information like name, email, and phone number.

We can confirm it worked by querying the table:

mysql> SELECT * FROM contacts;

This should display the sample data we inserted. Now we have a database and table ready to demonstrate using indexes.

+----+------------+-----------+--------------------+---------------+
| id | first_name | last_name | email              | phone         |
+----+------------+-----------+--------------------+---------------+   
|  1 | John       | Doe       | [email protected]   | 555-555-5555  |
|  2 | Jane       | Doe       | [email protected]   | 555-555-5556  |
|  3 | Bob        | Smith     | [email protected]    | 555-555-5557  |
+----+------------+-----------+--------------------+---------------+
3 lignes trouvées (0.00 sec)

Using Single-Column Indexes

A standard index in MySQL indexes one column of a database table. This allows fast lookups and sorts on that particular column.

To add an index to a column, we can use the CREATE INDEX statement:

mysql> CREATE INDEX idx_last_name ON contacts(last_name);

This adds an index named idx_last_name on the last_name column of the contacts table.

Indexing the last name column can optimize queries like this that filter by a last name:

mysql> SELECT * FROM contacts WHERE last_name='Doe';

Instead of scanning every row, MySQL can quickly lookup the indexed last_name column to find relevant rows.

+----+------------+-----------+--------------------+---------------+
| id | first_name | last_name | email              | phone         |
+----+------------+-----------+--------------------+---------------+
|  1 | John       | Doe       | [email protected]   | 555-555-5555  |
|  2 | Jane       | Doe       | [email protected]   | 555-555-5556  |
+----+------------+-----------+--------------------+---------------+
2 rows in set (0.00 sec)

This shows the rows returned from the contacts table where the last name is ‘Doe’. The index on the last_name column allows this query to run quickly without scanning the entire table.

We can add similar indexes on other table columns like email or phone:

mysql> CREATE INDEX idx_email ON contacts(email);
mysql> CREATE INDEX idx_phone ON contacts(phone); 

This allows fast lookups by those columns too.

To delete an index when no longer needed, use DROP INDEX:

mysql> DROP INDEX idx_email ON contacts;

Single-column indexes work best on columns frequently used for lookups and joins. Don’t over-index all columns, as indexes take up storage space and slow write operations like INSERT and UPDATE as the indexes also must be updated.

Using Unique Indexes to Prevent Data Duplication

In many cases, we want to prevent duplicate values from being stored in certain columns, such as email addresses or usernames. MySQL provides a special UNIQUE index that enforces this constraint:

mysql> CREATE UNIQUE INDEX idx_email ON contacts(email);

This index only allows unique email values to be inserted into that column. If we try to insert a duplicate email:

mysql> INSERT INTO contacts (first_name, last_name, email, phone)
VALUES ('Bob', 'Jones', '[email protected]', '555-555-5558'); 

We would get an error:

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'idx_email'

The unique index prevented inserting the duplicate email. This helps enforce data integrity in important columns.

Like other indexes, the UNIQUE index still provides fast lookups by the indexed column. The uniqueness is just an added constraint.

Using Indexes on Multiple Columns

Indexes can also be created that span multiple columns. This allows optimizing queries that filter on those columns in the same order.

For example, to index both first and last name columns:

mysql> CREATE INDEX idx_name ON contacts(first_name, last_name);

This can optimize a query with a WHERE clause on both columns:

mysql> SELECT * FROM contacts WHERE first_name='Jane'AND last_name='Doe';

It also works for queries filtering just the first column:

mysql> SELECT * FROM contacts WHERE first_name='Jane';

But it would not optimize a query filtering only on the second indexed column:

mysql> SELECT * FROM contacts WHERE last_name='Doe';

In that case the single-column index on last_name would be used instead.

The order of columns in a multiple-column index matters. The optimizations apply to WHERE clauses with filters on prefixes of the index columns.

Listing and Removing Existing Indexes

To help understand what indexes exist on a table, we can query the INFORMATION_SCHEMA system database:

mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA ='contacts'AND TABLE_NAME ='contacts';

This would display metadata about the indexes on our table, including their names and columns. 

For example:

+------+------------+------------+------------+--------------+-------------+----------+------------+----------+--------+------+------------+---------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT |
+------+------------+------------+------------+--------------+-------------+----------+------------+----------+--------+------+------------+---------+---------------+
| def  | contacts   | contacts   |          0 | contacts     | PRIMARY     |            1 | id         | A        |           3 |     NULL | NULL   |         | BTREE      |         |  
| def  | contacts   | contacts   |          1 | contacts     | idx_email   |            1 | email      | A        |           3 |     NULL | NULL   | YES    | BTREE      |         |
+------+------------+------------+------------+--------------+-------------+----------+------------+----------+--------+------+------------+---------+---------------+

When an index is no longer needed, we can remove it with DROP INDEX:

mysql> DROP INDEX idx_name ON contacts;

Dropping indexes that are not optimized and regularly used can help improve write performance and reduce storage requirements.

Conclusion

Adding indexes provides powerful optimizations for querying and manipulating data in MySQL tables. The right indexes can greatly speed up lookups, filters, sorting and joins.

Some key points to remember:

• Use single-column indexes for frequently filtered columns
• UNIQUE indexes prevent duplicate entries in a column
• Multiple-column indexes optimize queries filtering on those columns in order
• Don’t over-index! Evaluate query needs and drops indexes that aren’t used.

Properly leveraging indexes is crucial for optimal MySQL database performance, especially as data grows larger. Take time to understand indexing tradeoffs and best practices when designing your database schema and queries.