Optimizing MySQL Indexes

The management of indexes—how they are created and maintained—can impact the performance of SQL statements.

Combining Your DDL

An important management requirement when adding indexes to MySQL is the blocking nature of a DDL statement. Historically, the impact of an ALTER statement required that a new copy of the table be created. This could be a significant operation for time and disk volume when altering large tables. With the InnoDB plugin, first available in MySQL 5.1, and with other third party storage engines, various ALTER statements are now very fast, as they do not perform a full table copy. You should refer to the system documentation for the specific storage engine and MySQL version to confirm the full impact of your ALTER statement.

Combining multiple ALTER statements into one SQL statement is an easy optimization improvement. For example, if you needed to add a new index, modify an index, and add a new column, you could perform the following individual SQL commands:

ALTER TABLE test ADD INDEX (username);
ALTER TABLE test DROP INDEX name, ADD INDEX name (last_name, first_name);
ALTER TABLE test ADD COLUMN last_visit DATE NULL;

You can optimize this SQL by combining all statements for a single table into one SQL statement:

ALTER TABLE test
ADD INDEX (username),
DROP INDEX name,
ADD INDEX name (last_name, first_name),
ADD COLUMN last_visit DATE NULL;

This optimization can result in significant performance improvement of administration tasks.

Removing Duplicate Indexes

A duplicate index has two significant impacts: All DML statements will be slower as additional work is performed to maintain the data and index consistency. Additionally, the disk footprint of the database is now larger and can lead to increased backup and recovery time.

Several simple conditions can cause duplicate indexes. MySQL does not require a primary key column also to be indexed. Here is an example:

CREATE TABLE test(
  id INT UNSIGNED NOT NULL,
  first_name VARCHAR(30) NOT NULL,
  last_name  VARCHAR(30) NOT NULL,
  joined     DATE NOT NULL,
  PRIMARY KEY(id),
  INDEX (id)
);

In this DDL the defined index on id is a duplicate and should be removed.

A duplicate index also exists when the leftmost portion of a given index is contained within another index. Here is an example:

CREATE TABLE test(
  id INT UNSIGNED NOT NULL,
  first_name VARCHAR(30) NOT NULL,
  last_name  VARCHAR(30) NOT NULL,
  joined     DATE NOT NULL,
  PRIMARY KEY(id),
  INDEX name1 (last_name),
  INDEX name2 (last_name, first_name)
);

The name1 index is redundant as the index columns are contained within the leftmost portion of the name2 index.

The Maatkit mk-duplicate-index-checker command is one open source tool that can quickly review your database schema for duplicate indexes. Refer to http://maatkit.org/mk-duplicate-key-checker for more information.

Removing Unused Indexes

In addition to duplicate indexes that are unused, other defined indexes might be unused. These indexes have the same performance impact as duplicate indexes. The official MySQL product provides no means to identify what indexes are unused; however, several MySQL variants do provide this feature.

The Google MySQL patch (http://code.google.com/p/google-mysql-tools/wiki/Mysql5Patches) first introduced the SHOW INDEX_STATISTICS functionality. This feature is part of a number of new commands that measure per user monitoring.

For the official MySQL product, to determine unused indexes, you first need to collect all SQL statements executed. By using these SQL statements to capture and aggregate the Query Execution Plan (QEP) for all SQL statements, per table analysis will provide information about unused indexes. The process of SQL capture and QEP generation is a good practice for all applications.

Monitoring Ineffective Indexes

When defining multi column indexes, it is important that you determine the true effectiveness of all columns specified. This instrumentation is also not part of the official MySQL product.

Analysis of the key_len column for all SQL statements on a given table can identify any indexes that might contain unused columns.

Debugging composite indexes in MySQL with EXPLAIN

The composite index in MySQL is an index on multiple columns. This kind of indexes may increase the performance of a query if it tests multiple columns in its WHERE or ORDER BY sections. Let’s see an example of such a query:

SELECT * FROM product WHERE price = 100 AND size = 'Medium'

As it reads in the query, in the WHERE clause we test two different columns — price and size. Normally MySQL can use only one index per single query, so if we create two separate indexes for price and size, it won’t help — only one of them can be used in an efficient way. For example, if we had the price and size indexes, EXPLAIN output for the query above would look like this (only part of the output is shown):

Or, even worse, MySQL is very likely to decide to perform the index_merge operation on these two indexes, which usually affects performance badly (it’s a very costly operation itself).

So it seems to be the right time to add a composite index to those two columns. It’s done in a very simple way:

CREATE INDEX price_size ON products (price, size)

All good! Now it’s time to check if our index is applied correctly. Let’s run EXPLAIN with the query from the previous example:

EXPLAIN SELECT * FROM product WHERE price = 100 AND size = 'Medium'

Here we can see that our index has been used successfully. Let’s pay special attention to the key_len field. It shows the index length in bytes used in the query. The price part of the index is 6 bytes (on a DECIMAL(10, 2) column), and the size is 153 bytes (on a VARCHAR(50) column), so it makes a sum of 159 we can see in the output.

Well, a condition like price = 100 doesn’t seem to be useful in real life. We’d rather filter our table with something like price > 100. Let’s EXPLAIN this new query:

EXPLAIN SELECT * FROM product WHERE price > 100 AND size = 'Medium'

Have you noticed a change? The key_len is just 6 now. But it was 159! It means that the index hasn’t been used fully. It’s because of the way how composite indexes are stored internally — we can roughly compare it to having the values from price and size concatenated to the same string. So for such query, MySQL can use the first part of the index (price), but can’t use the second part.

To solve this, we need to add an index with the same columns, but in a different order:

CREATE INDEX size_price ON products (size, price)

Is makes more sense, because we do the = comparison with the size column, so when the index starts with its value, MySQL then is also able to use the other part of this index to get the records where price > 100:

EXPLAIN SELECT * FROM product WHERE price > 100 AND size = 'Medium'

So, we conclude that the key_len field in MySQL EXPLAIN output helps us to check if a composite index has been used fully. Also, we see that if our queries use both = and < (or any other non-equal comparison), it would be better to create a composite index beginning with the column we test with = in the queries.

How To Reverse String In MySQL

Many SQL queries involve string manipulation. The data residing in string data-type columns need to be massaged and transformed in a variety of ways like extracting portions of strings, hashing strings, changing the case of strings and so on. All this is achieved in MySQL through built-in string functions. In this article, we will learn how to reverse a string in MySQL.

MySQL Reverse Function – REVERSE()

The function in MySQL to reverse a string is REVERSE() . The syntax is as follows:

reverse(exp)

where exp is a valid string expression like a column, a variable or an expression involving columns and variables. 

Some examples are:

REVERSE(first_name) 

where first_name is a column of the table.

REVERSE (CONCAT(first_name, ' ', last_name)

 where first_name and last_name are columns of the table.

Therefore we get the following from REVERSE()

SELECT REVERSE('SIR')                 ---> 'RIS'

SELECT REVERSE('madam rotor')  -->  'rotor madam'

SELECT REVERSE ('palindrome')    -->  'emordnilap'

MySQL REVERSE() function is safe to be used with string data types like varchars and text and is multibyte safe ie. those encodings that utilize multiple bytes for character representations can be safely  used with REVERSE() function

CAUTION: If the argument expression to the REVERSE () function is not a string but is a number or a float then the function doesn’t fail and instead reverses the digits of the number.

Therefore:

SELECT REVERSE(134)     --> 431

SELECT REVERSE(7534.32) --> 23.4357

Reverse Function in SQL Practical Use Cases

Reverse() Function is a function which has limited practical uses. It doesn’t lend itself to many use cases. Some occasions in which it is utilized is:  

1.  Cryptography.
2.  Optimizing regular expression searches where you need to find the last occurrence of a pattern in a string.

SQL Reverse Example

Let’s create a small demonstration of SQL  Reverse function using tables.

1. CREATE a table T1.

CREATE TABLE T1 (ID INT, PRODUCT_NAME VARCHAR(100), PRICE DECIMAL(6,2));

2. INSERT sample data into the table.

INSERT INTO t1 (id, product_name, price) values (1001, 'Trustbasket', 1400.50);

INSERT INTO t1 (id, product_name, price) values (1002, 'Lakewood Croquet Set', 1233.65);

INSERT INTO t1 (id, product_name, price) values (1003, 'Desert Roll Set', 560.24);

3. Run SELECT query on T1 to view the data present in it.

SELECT * FROM T1;

Data from table T1

4. Now run the query with REVERSE function on columns.

SELECT REVERSE(PRODUCT_NAME) AS PROD_REVERSE, REVERSE(PRICE) AS PRICE_REVERSE, REVERSE(CONCAT(PRODUCT_NAME, '-' , PRICE)) CONCAT_REVERSE

FROM T1;

Output of REVERSE function on table T1

How to work with Index Hints in MySQL

MySQL index hints are used in SELECT statement conditions to manage indexes using the USE INDEX, FORCE INDEX and IGNORE INDEX commands. These commands transfer the index information to the optimizer and modify the control of the query execution. If used correctly, index hints will indicate which index should be used and can minimize execution time. In this article, we discuss how to use index hints in MySQL.

Enhancing queries using index hints can only be used on SELECT statements. Even though it can be used in UPDATE statements without an error, there is no effect.  

MySQL Index Hints Syntax

The following is the syntax for using index hints in MySQL:

1. SELECT column(s)
3. FROM table(s)
5. { USE | IGNORE | FORCE } INDEX ( index1, index2, … )
7. WHERE condition(s)

For the following example, the SALES table will be used with the index hints syntax, forcing SQL to use only the indexes mentioned in the USE INDEX command to return the rows within the table:

SALES Table:

SalesID	CustomerID	ZoneID	ProductID	ProductPrice	PrdQty	Total
1010	111011	1	10	20	3	60
1011	111012	3	20	130	1	130
1012	111011	1	10	20	5	100
1013	111013	2	30	150	2	300

USE INDEX

In the USE INDEX command, the parameters specified are indexes which are recommended to be used. Note that indexes listed from this command can be avoided, which will imply that the query plan will not use any indexes for this execution.

Our sample query is as follows:

1. SELECT * FROM SALES USE INDEX (SalesIndX, CustIndX)
3. WHERE SalesID = 1010 AND CustId = 111011 AND ZoneId = 1;

IGNORE INDEX

In the IGNORE INDEX command, the parameters specified refer to indexes that should be avoided. Using this command will force MySQL to use all the other indexes in the existing table but not the one mentioned in the query statement:

Our sample query is as follows:

1. SELECT * FROM SALES IGNORE INDEX (ZoneIndX)
3. WHERE SalesID = 1010 AND CustId = 111011 AND ZoneId = 1;

FORCE INDEX

In the FORCE INDEX command, the parameters specified are the only indexes allowed to be used in the query execution. This option should be used when the optimizer does not use the correct index that it’s being defined in the WHERE condition, even if we used the USE INDEX option already.

Our sample query is as follows:

1. SELECT * FROM SALES FORCE INDEX (ProductPriceIndX)
3. WHERE ProductPrice BETWEEN 100 AND 200;

Note: The FORCE INDEX command is used to avoid full table scans on tables that hold big data amounts.

USE INDEX / IGNORE INDEX / FORCE INDEX Conditions

The following specifies how the index hints commands are used:

• In the FORCE INDEX and IGNORE INDEX command, the index list must be declared. Let’s look at the following query:

1. SELECT * FROM table_name FORCE INDEX;

 This syntax will result in the following error:

[Error: PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FORCE INDEX' at line 1]

•  The USE INDEX and FORCE INDEX command cannot be used together, accessing the same table. Let’s look at the following query:

1. SELECT * FROM table_name USE INDEX (index1) FORCE INDEX (index1);

 This syntax will result in the following error:

[Error: PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USE INDEX (index1) FORCE INDEX (index1)' at line 1]

•  We can have the USE INDEX and IGNORE INDEX commands in the same statement. Let’s look at the following query:

1. SELECT * FROM table_name USE INDEX (index1) IGNORE INDEX (index2);

• Multiple index hints of the same type can be used in the same query. Let’s look at the following query:

1. SELECT * FROM table_name USE INDEX (index1) USE INDEX (index2);

• To define the scope of the index hint, add the clause FOR. There are three specific uses for it: FOR JOIN, used for join processes, FOR ORDER BY and FOR GROUP BY to manipulate how indexes work while grouping or sorting rows.

If the scope is not specified, MySQL will use the index hints on all of these.

1. SELECT * FROM table_name
3. USE INDEX FOR JOIN (index1)
5. USE INDEX FOR ORDER BY (index2)
7. USE INDEX FOR GROUP BY (index2);