Optimizing MySQL Indexes for Query Calculations

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 Queries with Indexes
• MySQL Query Optimization Techniques
• Summary

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

• Optimizing MySQL Queries with Indexes
• Optimizing MySQL Queries for Maximum Speed

Summary

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.