Optimize MySQL Queries

Optimize MySQL Queries

Optimize MySQL Queries : Multi-thousand tips to make your life easier.

If querying takes a lot of time and database connections and backlogs are growing, then it is time to optimize your database. In MySQL, you can use the mysqlreport command to generate a report on a sufficiently used database and establish a baseline for further actions. You may also need to modify the configuration file (my.cnf) to log slow queries. Identify the slow-running queries and their possible causes. Usually, the causes are related to indexes, such as no index or improper index. This article gives a detailed description of which commands and concepts we can use to optimize MySQL.

Step 1: Using Indexes

Using indexes
Using indexes makes a query faster in the same way an index in a book helps you look up a particular word.

To know how and which indexes are used in a given query, use the EXPLAIN command as a prefix to the SELECT query.

EXPLAIN SELECT emp_id FROM ORGN_DATA WHERE dept_code = 'IT';

This command returns the following information:
Table – Which table(s) are used in the query
Type – JOIN type. Values can be system, const, eq_ref, ref, range, index, all
Possible Keys – All keys which can be used for indexes
Key – The key actually used for the index
Key Length – Shorter keys are better
Ref – Other columns used with the key to get the results
Rows – Number of rows from which data will come
Extra – Extra information. Some possible values can be using index, using where, using temporary, using filesort

You should reconsider your table structure if Possible Keys contains a NULL value.
A NULL value in the Key field indicates that no index is being used. Using no indexes will hurt performance if there are too many records in the table because, for any query, all records will be searched for the given condition.

Use the ALTER command to add an index as shown below:

ALTER TABLE table_name ADD INDEX (column_name);

You can index multiple columns using:

ALTER TABLE table_name ADD INDEX (column_name1, ..., column_nameN);

MySQL uses leftmost prefixing, so indexes will automatically be added for column_name1, (column_name1, column_name2), ..., (column_name1, ..., column_nameN-1). This is quite helpful in situations like searching based on surname, so an index on (surname, firstname, middlename) will automatically add multiple indexes used for faster querying.

Points to remember:

• Choose the column(s) for indexing very carefully.
• Indexed fields should be used for searching, and the query should be reframed if any calculation is performed on indexed fields.
• The index itself takes up storage space.
• Each operation on the database requires updating the index as well.
• A smaller Key Length reported by the EXPLAIN command is better. A smaller key length means less storage space is required by the index file and less time is needed to search the index. For this, indexes can be set on part of columns. For example, INDEX(col1(10), col2(20)). Again, you have to judiciously decide what length of columns to use for indexing.

Step 2: Using the ANALYZE Command

Using the ANALYZE command
Use the ANALYZE command to generate key distribution for the table to be used by the MySQL query optimizer to decide which indexes are best to use in a query.

ANALYZE TABLE table_name;

Step 3: Using the OPTIMIZE Command

Using the OPTIMIZE command
If there are too many inserts/deletes in a table, then the OPTIMIZE command must be used frequently to optimize disk head movement during retrieval. This is particularly useful if variable-size columns are used, such as varchar, text, blob, etc. The OPTIMIZE command performs defragmentation of the fragmentation caused by variable-length fields like VARCHAR.

OPTIMIZE TABLE table_name;

Step 4: Using Special Functions for Loading Data

Using special functions for loading data
For loading data from a file into a database table, using LOAD DATA INFILE is much faster than reading data from the file iteratively and using the INSERT command. The syntax for this command is:

LOAD DATA INFILE 'filedata.dat' INTO TABLE table_name (col1, col2, ..., colM) FIELDS TERMINATED BY '|';

Step 5: Setting the PRIORITY of Commands

Setting the PRIORITY of commands
If there are more queries than data insertions, you may lower the priority of the INSERT command using:

INSERT LOW_PRIORITY or SELECT HIGH_PRIORITY

If the client is not interested in the results of INSERT, then it can be immediately freed using the following command:

INSERT DELAYED.

This makes the system faster by batching insertions.

Step 6: DELETE vs. TRUNCATE

DELETE vs. TRUNCATE
DELETE deletes row by row, while TRUNCATE deletes all rows at once. So, if you are not interested in the number of rows deleted from a table as a result of a DELETE command, then use TRUNCATE with the following syntax:

TRUNCATE TABLE table_name;

Step 7: Lowering Permission-Checking Overhead

Lowering permission-checking overhead
Simpler permissions reduce permission-checking overhead. Use the GRANT command to set the permissions. The general syntax for GRANT is:

GRANT Oper1, ..., OperN ON db_name.tb_name TO user_name@computer_name IDENTIFIED BY password

For example:

GRANT SELECT, UPDATE ON EMP_DET.SALARY_DET TO feroz@localhost IDENTIFIED BY 'abc123';

If the user account 'feroz' does not exist, it will be created on localhost (although the user can access data from any computer), and the password 'abc123' will be assigned to it.

Step 8: Use the BENCHMARK() Function

Use the BENCHMARK() function
To know how much time a given MySQL function or expression takes, use the MySQL built-in function:

BENCHMARK(loop_count, expression)

It always returns 0 but also prints the time taken by the expression.

Step 9: Synchronizing Data Types

Synchronizing data types
If columns in tables contain identical information, then make their data types the same so that joins can be performed faster.

Step 10: Miscellaneous

Query optimization is not a one-time job. If your database is heavily used, then you may have to repeat the optimization process every 5-6 months.

Syed Feroz Zainvi maintains his technical blogs at:

http://www.crossroadsbyzainvi.blogspot.com