Optimizing MySQL Queries with Indexes

Part 1: Optimize MySQL Queries and Indexes

Indexes are vital in databases to quickly find the data needed. Without proper indexing, retrieving data can take a long time, especially as the amount of data grows. You would need to go through each page, which can lead to higher costs and the use of more resources.

To address this issue, analyzing how the database is used and understanding which queries are run most can help identify and optimize MySQL queries with indexes. Tools like query execution plans can also help. Creating the proper indexes based on this analysis can reduce query response times and improve the database performance.

• Optimizing MySQL Queries with Indexes
• MySQL Query Optimization Techniques
• Summary

Optimizing MySQL Queries with Indexes

Consider this example:

CREATE TABLE employee (
 employee_number char(10) NOT NULL,
 firstname varchar(40),
 surname varchar(40),
 address text,
 tel_no varchar(25),
 salary int(11),
 overtime_rate int(10) NOT NULL
);

To find employee Fred Jone's salary(employee number 101832), you run:

SELECT salary FROM employee WHERE employee_number = '101832';

MySQL has no clue where to find this record. It doesn't even know that if it does find one matching, there will not be another matching one, so it has to look through the entire table, potentially thousands of records, to find Fred's details.

An index is a separate file that is sorted and contains only the field/s you're interested in sorting. If you create an index on employee_number, MySQL can find the corresponding record very quickly (Indexes work in very similar ways to an index in a book. Imagine paging through a technical book (or, more often, a scrambled pile of notes!) looking for the topic "Optimizing MySQL". An index saves you an immense amount of time!

Before we repair the table structure above, let me tell you about a most important little secret for anyone serious about optimizing their queries:

EXPLAIN is the keyword in mysql that will help you figure out what is wrong.

EXPLAIN shows (explains) how your queries are being used. By putting it before a SELECT, you can see whether indexes are being used properly and what kind of join is being performed.

For example:

EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number = '10875';

+----------+------+---------------+------+---------+------+------+------------+
| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL  | NULL          | NULL |    NULL | NULL |    2 | where used |
+----------+------+---------------+------+---------+------+------+------------+

So what are all these things?

• table shows us which table the output is about (for when you join many tables in the query)
• type is an important one - it tells us which type of join is being used. From best to worst, the types are: system, const, eq_ref, ref, range, index, all
• possible_keys Shows which possible indexes apply to this table
• key And which one is actually used
• key_len gives us the length of the key used. The shorter, the better.
• ref Tells us which column, or a constant, is used
• rows Number of rows MySQL believes it must examine to get the data
• extra Extra info - the bad ones to see here are "using temporary" and "using filesort"

Looks like our query is a shocker, the worst of the worst! There are no possible keys to use, so MySQL has to go through all the records (only 2 in this example, but imagine a really large table with millions of records).

Now, let's add the index we talked about earlier.

If we re-run the EXPLAIN, we get:

+----------+-------+---------------+---------+---------+-------+------+-------+
| table       | type   | possible_keys   |   key     | key_len  | ref      | rows | Extra   |
+----------+-------+---------------+---------+---------+-------+------+-------+
| employee | const  | PRIMARY         | PRIMARY |       10 | const   |    1   |          |
+----------+-------+---------------+---------+---------+-------+------+-------+

The query above is a good one (it almost falls into the category of "couldn't be better"). The type of "join" (not really a join in the case of this simple query) is "const", which means that the table has only one matching row. The primary key is being used to find this particular record, and the number of rows MySQL thinks it needs to examine to find this record is 1. All of this means MySQL could have run this query thousands of times in the time it took you to read this little explanation.

MySQL Query Optimization Techniques

• Optimizing MySQL Indexes for Query Calculations
• Optimizing MySQL Queries for Maximum Speed

Summary

It's important to remember that different indexes serve different purposes. Choosing the correct type of index based on the specific use case is crucial. Over-indexing can also degrade performance because each index needs to be updated whenever data changes.

Optimizing MySQL queries with indexes or fixing poorly defined or non-existent indexes can dramatically improve database performance, making applications faster and more responsive. It's a critical aspect of database administration and optimization that, when done correctly, leads to significant improvements in data retrieval times and overall system efficiency.