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
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
Part 2: Optimize MySQL Queries and Indexes
When dealing with MySQL databases, optimizing your indexes ensures your queries run as efficiently as possible. Understanding the trade-offs involved is important, especially when updating tables with indexes.
Every time a table is updated, its associated indexes must also be updated. This process can impact performance since the system has to do extra work to keep the indexes current. However, this performance cost is often justified by the benefits indexes bring to query operations, especially when your system’s workload involves many select queries instead of insert operations.
Optimizing MySQL Indexes for Query Calculations
In scenarios where the system performs many more insert operations, and the speed of these inserts is a priority over the speed of select queries, the performance cost of updating indexes might seem less justifiable. This is because the overhead of maintaining the indexes could slow down the insert operations. However, for most applications where select operations dominate or are as significant as insert operations, the cost of maintaining the indexes is a worthwhile investment for the overall efficiency and speed it brings to data retrieval processes.
What about if you want to select more than one criterion? (As you can see, indexing those fields you use in the WHERE clause makes sense.) The query:
SELECT firstname FROM employee;makes no use of an index at all. An index on firstname is useless. But,
SELECT firstname FROM employee WHERE surname="Madida";would benefit from an index on surname.
Let’s look at some more complex examples where EXPLAIN can help us improve the query. We want to find all the employees where half their overtime rate is less than $20. Knowing what you do, you correctly decide to add an index on overtime_rate, seeing as that’s the column in the where clause.
ALTER TABLE employee ADD INDEX(overtime_rate);Now, let’s run the query.
EXPLAIN SELECT firstname FROM employee WHERE overtime_rate/2<20;
+----------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL | NULL | NULL | NULL | NULL | 2 | where used |
+----------+------+---------------+------+---------+------+------+------------+Not good at all! Every single employee record is being read. Why is this? The answer lies in the “overtime_rate/2” part of the query. Every overtime_rate (and hence every record) has to be read in order to divide it by 2. So, we should try to leave the indexed field alone and not perform any calculations. How is this possible? This is where your school algebra comes to the rescue! You know that ‘x/2 = y’ is the same as ‘x = y*2’.We can rewrite this query by seeing if the overtime_rate is less than 20*2. Let’s see what happens.
EXPLAIN SELECT firstname FROM employee WHERE overtime_rate<20*2;
+--------+-------+---------------+---------------+---------+------+------+----------+
|table | type | possible_keys | key | key_len | ref | rows |Extra |
+--------+-------+---------------+---------------+---------+------+------+----------+
|employee| range | overtime_rate | overtime_rate | 4 | NULL | 1 |where used|
+--------+-------+---------------+---------------+---------+------+------+----------+Much better! MySQL can perform the 20*2 calculation once and then search the index for this constant. The principle here is to keep your indexed field standing alone in the comparison so that MySQL can use it to search and not have to perform calculations on it.
You may say I was being unfair and should have phrased the request as “where the overtime rate is less than 40”, but users seem to have a knack for making a request in the worst way possible!
MySQL Query Optimization Techniques
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.
Optimizing MySQL indexes for query calculations is crucial for enhancing the efficiency of database operations. The performance costs associated with maintaining indexes, particularly in environments with frequent updates, are often far outweighed by the significant improvements they bring to query performance. A strategic approach to indexing, informed by an understanding of the specific needs of your database operations—whether they lean more towards insert operations or select queries—can lead to substantial gains in speed and efficiency. Moreover, the judicious use of indexes, especially in crafting queries that avoid unnecessary calculations on indexed fields, can further optimize performance. Ultimately, a well-considered indexing strategy is an invaluable investment in your database system’s overall health and speed.
Write A Comment