MySQL Performance Tuning?

 https://www.youtube.com/watch?v=ZH03kXN5-Ok

 Optimizing SELECT Statements

10.2.1.1 WHERE Clause Optimization

10.2.1.2 Range Optimization

10.2.1.3 Index Merge Optimization

10.2.1.4 Hash Join Optimization

10.2.1.5 Engine Condition Pushdown Optimization

10.2.1.6 Index Condition Pushdown Optimization

10.2.1.7 Nested-Loop Join Algorithms

10.2.1.8 Nested Join Optimization

10.2.1.9 Outer Join Optimization

10.2.1.10 Outer Join Simplification

10.2.1.11 Multi-Range Read Optimization

10.2.1.12 Block Nested-Loop and Batched Key Access Joins

10.2.1.13 Condition Filtering

10.2.1.14 Constant-Folding Optimization

10.2.1.15 IS NULL Optimization

10.2.1.16 ORDER BY Optimization

10.2.1.17 GROUP BY Optimization

10.2.1.18 DISTINCT Optimization

10.2.1.19 LIMIT Query Optimization

10.2.1.20 Function Call Optimization

10.2.1.21 Window Function Optimization

10.2.1.22 Row Constructor Expression Optimization

10.2.1.23 Avoiding Full Table Scans

 What Are the Benefits of MySQL Performance Tuning?

MySQL performance tuning offers significant benefits, such as improved efficiency, scalability, reduced downtime risks, and enhanced user experience. Optimization helps maximize a MySQL database's potential, ensuring it is reliable and performs well.

The key benefits of performance tuning are:

• Maximized resource utilization. Optimizes hardware resource allocation for better efficiency and cost-effectiveness.
• Enhanced indexing strategies. Boosts query performance by improving indexing techniques.
• Reduced downtime risks. Identifies and mitigates potential bottlenecks to minimize downtime and ensure continuous availability.
• Optimized cache configuration. Configures caching settings to enhance data retrieval speed and system responsiveness.
• Streamlined backup and restore processes. Facilitates backup and restore operations without impacting production systems.
• Improved query optimization. Enhances query execution plans and SQL statements for maximum efficiency.
• Enhanced security and compliance. Improves security configurations to mitigate risks and ensures compliance with standards.

Avoid Functions In Predicates

Avoid using functions in predicates (such as WHERE clauses) because MySQL might not be able to utilize indexes efficiently, resulting in slower query execution times.

For example, in the following statement:

SELECT * FROM MYTABLE WHERE UPPER(COL1)='123'Copy

The UPPER notation creates a function that must operate during the SELECT operation. This doubles the work the query is doing and puts additional stress on the database server. Additionally, the UPPER function prevents the database from utilizing the index on COL1.

If you cannot avoid that function in SQL, create a new function-based index or generate custom columns in the database to improve performance.

Avoid Wildcard (%) at the Beginning of Predicates

Wildcards perform a broader search when searching through textual data. For example, to select all names that start with ch, create an index on the name column and execute the following statement:

SELECT * FROM person WHERE name LIKE "ch%"

The query scans the indexes, lowering the query cost:

However, searching for names using wildcards at the beginning increases the query cost significantly because an indexing scan does not apply to the ends of strings:

Instead of indexing, wildcards at the beginning perform full table scan searches through each row individually, increasing the query cost in the process. In the example query, using a wildcard at the end helps reduce the query cost due to going through fewer table rows.

Note: Check out our MySQL Commands Cheat Sheet, which features the indexing commands.

A clever way to search the ends of strings is to reverse the string, index the reversed strings, and look at the starting characters. Placing the wildcard at the end now searches for the beginning of the reversed string, making the search more efficient.

System Hardware Tuning

Another option for boosting performance is to adjust hardware and software options at the system level. For example:

• Storage. If you use traditional hard disk drives (HDD), upgrade to solid-state drives (SSD) for performance improvements. Use a tool like iotop or sar from the sysstat package to monitor disk input/output rates. If disk usage is higher than the usage of other resources, consider adding storage or upgrading to a faster storage solution.
• Processor. Processors are considered the measure of your system's speed. Use the Linux top command to see how your resources are used. Pay attention to the MySQL processes and their processor use percentage. Processors are more expensive to upgrade, but if your CPU is a bottleneck, an upgrade might be necessary.

• Memory. Memory is the total amount of RAM in your MySQL database storage server. You can adjust the memory cache to improve performance. If you don't have enough memory or the existing memory is not optimized, you can end up hurting your performance instead of improving it. If your server constantly runs out of memory, you can upgrade by adding more.
• Network. Monitor network traffic to ensure the infrastructure is sufficient to handle the workload. Overloading your network can lead to high latency, dropped packets, and server outages. Ensure there is enough network bandwidth to accommodate normal database traffic levels and potential spikes during peak hours.

Tune MySQL for Your Hardware

Another way to boost a MySQL server's performance is to tune it to your hardware. Configuring the server involves changing the /etc/mysql/my.cnf file. You can edit the file with any text editor using sudo. Proceed with caution and make minor changes at a time.

The following list contains some variables in the my.cnf file that impact server performance:

• max_connection - States the maximum number of simultaneous connections allowed. If you are getting errors citing "Too many connections," increasing this value may help.
• innodb_buffer_pool_size - Allocates system memory as a data cache for your database. If you have large chunks of data, increase this value. Take note of the RAM required to run other system resources.
• innodb_io_capacity - Sets the rate for the storage device input/output. It is directly related to the storage drive type and speed. A 5400-rpm HDD will have a much lower capacity than a high-end SSD or Intel Optane. Adjust this value to match your hardware better.
• query_cache_size - As mentioned in the caching strategies section, this item specifies the cache size for MySQL queries waiting to run.
• innodb_stats_on_metadata - Causes InnoDB to update statistics when metadata statements such as SHOW TABLE STATUS or SHOW INDEX are executed. If statistics are unimportant, set it to OFF to avoid updating InnoDB statistics and improve read speeds.
• innodb_flush_method - Controls the data flushing method, which can impact performance, reliability, and compatibility with different storage configurations. Set it to O_DIRECT to avoid a performance penalty from double buffering.

Note: When adjusting configuration settings, it is best to make small, incremental adjustments. A major adjustment may overburden another value and degrade performance.
Make one change at a time and then test. It is easier to track errors or misconfigurations when you change one variable at a time.

Use InnoDB Instead of MyISAM

InnoDB configurations greatly affect MySQL performance. Some parameters and their effects, such as the innodb_buffer_pool_size and innodb_io_capacity, were discussed in the section above.

MyISAM is an older database style used for some MySQL databases. It is a less efficient database design than InnoDB, which supports more advanced features and has in-built optimization mechanics.

InnoDB uses a clustered index and keeps data in pages, which are stored in consecutive physical blocks. If a value is too large for a page, InnoDB moves it to another location and indexes it. This feature helps keep relevant data in the same place on the storage device, meaning it takes a physical hard drive less time to access data.

For more information, see our in-depth comparison between MyISAM and InnoDB.

Update MySQL to Latest Version

Using the latest version is not always feasible for older and legacy databases. But whenever possible, you should check your MySQL version and upgrade to the latest.

Performance enhancements are part of ongoing development. Newer MySQL versions may render some common performance adjustments obsolete. In general, it is always better to use native MySQL performance enhancement over scripts and configuration files.

Use Automatic Performance Improvement Tools

As with most software, not all tools work on all MySQL versions. Here are three utilities for evaluating your MySQL database and making changes to improve performance:

• Tuning-primer. The utility is an older tool designed for MySQL 5.5 - 5.7. It analyzes your database and suggests settings to improve performance. For example, it may suggest that you raise the query_cache_size parameter if it determines your system cannot process queries quickly enough to keep the cache clear.
• MySQLTuner. The second tuning tool is useful for modern SQL databases. The Perl script analyzes database configuration looking for bottlenecks and inefficiencies. The output shows metrics and recommendations. MySQLTuner works with MySQL 8.x.
• phpMyAdmin Advisor. Like the previous two utilities, phpMyAdmin Advisor evaluates your database and recommends adjustments. If you already use phpMyAdmin, the Advisor is a panel available within the app's GUI.

Note: Check out our list of top SQL query optimization tools and use our in-depth analysis of each one to find the best one for your tasks.

Specify Columns in SELECT Function

A commonly used expression for analytical and exploratory queries is SELECT *. Selecting more than you need results in unnecessary performance loss and redundancy. If you specify the necessary columns, your query will scan only the relevant ones.

Instead of using SELECT *, specify columns in the SELECT clause to improve MySQL performance. Excessive columns cause additional load on the database, slowing down the server's performance and overusing the resources.

Therefore, instead of using:

SELECT * FROM table

Use the syntax below:

SELECT column1, column2 FROM table

Use ORDER BY Appropriately

The ORDER BY expression sorts results by the specified column. It can also sort by two columns at once, ascending or descending.

If you try to sort different columns in different order, it will slow down performance. You may combine ORDER BY with an index to speed up the sorting.

GROUP BY Instead of SELECT DISTINCT

The SELECT DISTINCT query is useful when eliminating duplicate values. However, the statement requires a large amount of processing power. Additionally, it is efficient only in specific situations and can sometimes be confusing.

For example, if a table lists information about customers with the following structure:

id	name	lastName	address	city	state	zip
0	John	Smith	652 Flower Street	Los Angeles	CA	90017
1	John	Smith	1215 Ocean Boulevard	Los Angeles	CA	90802
2	Martha	Matthews	3104 Pico Boulevard	Los Angeles	CA	90019
3	Martha	Jones	2712 Venice Boulevard	Los Angeles	CA	90019

Running the following query returns four results:

SELECT DISTINCT name, address FROM person

The statement seems like it should return a list of distinct names and addresses. Instead, the query looks at both the name and address columns. Although two pairs of customers have the same name, their addresses differ.

To filter out duplicate names and return the addresses, try using the GROUP BY statement:

SELECT name, address FROM person GROUP BY name

The result returns the first distinct name along with the address, making the statement less ambiguous. To group by unique addresses, the GROUP BY parameter would just change to address and return the same result as the DISTINCT statement faster.

JOIN, WHERE, UNION, DISTINCT

Use INNER JOINs whenever possible. An OUTER JOIN looks at additional data outside the specified columns, which is often a waste of resources because it includes data that is not required.

Using INNER JOIN is the standard approach to joining tables. Most database engines accept using WHERE as well. For example, the following two queries output the same result:

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

And:

SELECT * FROM table1, table2 WHERE table1.id = table2.id

In theory, they have the same runtime as well.

The choice between JOIN and WHERE query depends on the database engine. While most engines have the same runtime for the two methods, in some database systems, one runs faster than the other.

The UNION and DISTINCT commands are sometimes included in queries. Like an outer join, it is fine to use these expressions if necessary. However, they add additional database sorting and reading operations. It is better to find a more efficient expression if they are not required.

Use the EXPLAIN Function

Modern MySQL databases include an EXPLAIN function.

Appending the EXPLAIN expression to the beginning of a query will read and evaluate the query. If there are inefficient expressions or confusing structures, EXPLAIN can help you find them. You can then adjust the query's phrasing to avoid unintentional table scans or other performance hits.

 =======================

6 Key MySQL Performance Tuning Tips

Unlocking the full potential of your MySQL database requires more than just its initial setup. To ensure your database operates at peak efficiency, you need to fine-tune its performance. In this section, we’ll explore six key MySQL performance tuning tips that can significantly enhance your database’s responsiveness, scalability, and overall efficiency.

1. MySQL Query Optimization

Improving MySQL query performance and minimizing query execution time is a crucial step in enhancing database efficiency. One effective strategy is query rewriting, where you restructure your SQL queries to be more efficient. This may entail eliminating unnecessary subqueries, simplifying intricate joins, and optimizing conditions in the WHERE clause. By fine-tuning your queries, you lighten the workload on your MySQL server, leading to quicker response times and an overall boost in database performance.

Indexing is another powerful technique for query optimization. Properly indexing your database tables can significantly accelerate query execution. By creating indexes on columns commonly used in WHERE clauses or JOIN operations, MySQL can quickly locate the relevant data, reducing the need for full table scans. Additionally, regularly analyzing and optimizing your indexes is essential to ensure they remain effective as your data evolves.

Query plan analysis can also provide valuable insights. MySQL provides tools to examine query execution plans, allowing you to identify bottlenecks, suboptimal join methods, or missing indexes. 

2. Monitor Resource Utilization

Monitoring and analyzing resource utilization in your MySQL database is crucial for maintaining optimal performance and preventing potential bottlenecks. Key metrics such as CPU usage, memory usage, and disk I/O offer insights into how efficiently your database server operates. 

High CPU usage, for example, can indicate that your server is under heavy processing load, possibly due to poorly optimized queries or increased user activity. 

Memory usage is another critical metric to watch because if your database server consistently uses a large portion of available memory, it might lead to slow query performance as data retrieval from disk becomes more frequent. Efficient memory management, including optimizing query caches and buffer pools, can help strike the right balance between memory consumption and query response times. 

Lastly, monitoring disk I/O is essential because slow I/O operations can severely impact database performance. By analyzing disk I/O metrics, you can optimize queries to reduce disk reads or upgrade to faster storage solutions.

3. Indexing Strategies

Indexing plays a pivotal role in database performance, and its impact on query performance cannot be overstated. When you search for specific data within a database table, an index allows the database engine to quickly pinpoint the relevant rows, significantly reducing the time it takes to retrieve information. Without proper indexing, queries would need to scan through the entire table, which can lead to slow and resource-intensive operations, especially in large datasets.

To maximize indexing benefits, be sure to follow best practices. Start by wisely selecting columns to index, focusing on those in frequent WHERE clauses or JOIN operations. Avoid over-indexing, which can bloat storage and slow writes. Next, pick the right index type, like B-tree, hash, or full-text, aligning with your needs.

Regular maintenance of indexes is also vital, especially as your database evolves. Periodically assess query performance to pinpoint areas for optimization and consider adding, removing, or modifying indexes. 

4. InnoDB Configuration

InnoDB configuration settings wield substantial influence over MySQL performance. Key parameters like the buffer pool size significantly impact efficiency by determining how much data MySQL can cache in memory for rapid access. Thread concurrency settings dictate the number of simultaneous connections MySQL can handle efficiently, and transaction isolation levels, such as Read Committed or Repeatable Read, affect how locking mechanisms operate, impacting concurrency and query execution speed.

5. Caching Mechanisms

Utilizing caching mechanisms is a potent technique for accelerating query response times within MySQL databases. These mechanisms operate by retaining frequently accessed data or query outcomes in memory, enabling subsequent requests for the same information to be retrieved significantly faster than the alternative of fetching it directly from disk.

One commonly employed caching strategy is query caching, wherein MySQL preserves the outcomes of SELECT queries alongside the respective query. Consequently, if an identical query is made later, the cached results can be swiftly delivered, reducing query execution time.

Another highly beneficial caching method is key-value caching. In this approach, specific data, such as frequently accessed database rows or objects, is stored in a caching system, which facilitates rapid data retrieval without the necessity to access the database

6. Regular Maintenance

Routine maintenance tasks are vital for maintaining the health and optimal performance of your database over time. Among these tasks, data pruning is a critical practice involving the periodic removal of outdated or unnecessary data from your database. By pruning, you can prevent the database from becoming bloated and experiencing performance degradation over time. Pruning also helps to meet compliance requirements for data retention policies, ensuring your database only contains relevant and valuable information.

Index reorganization is another essential maintenance activity because, over time, as data is inserted, updated, and deleted,  indexes can become fragmented or inefficient. Reorganizing them helps to maintain integrity and ensures that query performance remains optimized. 

Lastly, because statistics provide the query optimizer with information about data distribution, it’s important to update them regularly. Without up-to-date statistics, queries may be poorly optimized, leading to slower response times and decreased overall performance.

Ensure your databases are performing their best — today and tomorrow — with proactive database optimization and query tuning.

 

Breaking Down MySQL Performance Tuning

Initial MySQL tuning can be broken down to the following categories:

• Tuning for your hardware
• Tuning for best performance / best practices
• Tuning for your workload

Tuning MySQL for Your Hardware

Depending on the hardware you have installed MySQL on, some variables need to be set based on the machine (or VM) specifications. The following variables are largely dependent on your hardware:

innodb_buffer_pool_size

• Generally, set to 50% – 70% of your total RAM as a starting point.
• It does not need to be set any larger than the total database size.
• Percona Monitoring and Management (PMM) can offer additional insight, showing your buffer pool usage and allowing you to tune accordingly.
  • 
  • 
  • 

innodb_log_file_size

• This is generally set between 128M – 2G.
• Should be large enough to hold at most an hour or so of logs.
  • This is more than enough so that MySQL can reorder writes to use sequential I/O during the flushing and checkpointing processes.
• PMM can offer additional insight, as if you are using more than 50% of your log space, you may benefit from a log file size increase.
  • 

innodb_flush_log_at_trx_commit

• Setting to “1” (default in 5.7) gives the most durability.
• Setting to “0” or “2” will give more performance, but less durability.

innodb_flush_method

• Setting this to O_DIRECT will avoid a performance penalty from double buffering.

MySQL Tuning for Best Performance & Best Practices

innodb_file_per_table

• Setting this to “ON” will generate an independent InnoDB table space for every table in the database.

innodb_stats_on_metadata

• Setting this to “OFF” avoids unnecessary updating of InnoDB statistics and can greatly improve read speeds.

innodb_buffer_pool_instances

• A best practice is to set this to “8” unless the buffer pool size is < 1G, in which case set to “1”.

query_cache_type & query_cache_size

• Setting both of these to “0” will entirely disable the query cache.

MySQL Performance Tuning for Your Workload

To tune further, more information will be required. The best way to gather this information is to install a MySQL monitoring / graphing tool like Percona Monitoring and Management platform. Once you have a tool installed, we can dive into the individual metrics and start customizing based on the data.

I would recommend starting with one of the most impactful variables – the innodb_buffer_pool_size.  Compare the RAM and number of free pages on your instance to the total buffer pool size. Based on these metrics, you can determine if you need to increase or decrease your overall buffer pool size setting.

Next, take a look at your metrics for the InnoDB Log File usage. The rule of thumb is that your log files should hold approximately one hour of data. If you see that your data written to the log files hourly exceeds the total size of the log files, you would want to increase the innodb_log_file_size variable and restart MySQL. You could also verify with “SHOW ENGINE INNODB STATUS;” via the MySQL CLI to assist in calculating a good InnoDB log file size.

Exploring Further InnoDB Settings

Other InnoDB settings that can be further tuned for better performance are:

innodb_autoinc_lock_mode

• Setting this to “2” (interleaved mode) can remove the need for an auto-inc lock (at the table level) and can increase performance when using multi-row insert statements to insert values into a table with an auto increment primary key. Note that this requires either ROW or MIXED binlog format.

innodb_io_capacity / innodb_io_capacity_max

• These settings will impact your database if you are utilizing a write-heavy workflow. This does not apply to read (SELECT) traffic. To tune these values, it is best to know how many iops your system can perform. It is a good idea to run sysbench or another benchmark tool to determine your storage throughput.
• PMM can offer additional insight, showing your IO usage and allowing you to tune accordingly.
  • 

Maximizing MySQL Performance with Percona’s Managed Database Services

While this article may not cover everything on performance tuning, the suggestions above should clear some of the low hanging fruit and get your system closer to an ideal setup. As with all database tuning, your process should be an ongoing one based on current information.

• Examine the settings proposed above, and implement if they make sense for your environment/workload.
• Install a good MySQL monitoring tool to give insight into the database (Percona Monitoring and Management is our suggestion).
• Stay current on your monitoring graphs to determine other areas where you may need to tune.

---

MySQL Performance Tuning is an essential guide covering the critical aspects of MySQL performance optimization.

 

Download and unlock the full potential of your MySQL database today!

FAQ

What is MySQL performance tuning, and why is it important?

MySQL tuning encompasses the practice of enhancing the efficiency, responsiveness, and overall performance of a MySQL database. This optimization process entails fine-tuning database settings, configurations, and query performance to ensure that MySQL functions at its best.

How do I know if my MySQL database needs performance tuning?

Slow query response times, increased resource utilization, frequent database downtime, or a decline in overall system performance are signs that your MySQL database needs performance tuning. 

What are the key benefits of optimizing MySQL database performance?

Optimizing the performance of a MySQL database provides numerous benefits, including faster query execution, diminished resource utilization, increased scalability to accommodate expanding workloads, heightened user satisfaction, and cost savings by optimizing hardware resource usage. Additionally, it helps maintain the reliability and availability of your database.

What are the common performance issues in MySQL databases?

MySQL databases frequently encounter common performance challenges, including slow queries, ineffective indexing, resource conflicts, suboptimal configuration settings, and inadequate hardware resources. These issues can result in diminished database performance and can be improved through performance tuning.

Can you explain the importance of query optimization in MySQL?

Query optimization is crucial in MySQL because it significantly impacts database performance. Well-optimized queries execute faster, consume fewer resources, and reduce the load on the database server, all of which improve system performance and enhance the user experience. 

How can Percona help with MySQL performance tuning?

Percona offers expertise and solutions for MySQL performance tuning, and o

============================================

MySQL Server Performance Tuning with Tips for Effective Optimization

MySQL, being the most popular database relational database management system, still requires optimization from time to time. More than that, in case of big and complex data sets, regular optimization actions are essential for proper system performance.

MySQL performance optimization generally involves configuring, profiling, and monitoring performance at several levels. To tune MySQL performance, you do not necessarily need to have extensive expertise and a deep understanding of SQL.

In this article, we will take you through the major performance tunning techniques so that you could ensure the stability, reliability, and speed of your database-driven applications.

Check recommended hardware and software
requirements for MySQL

The first thing to be done, especially if you are an owner of a low-end PC, is to check the optimal hardware and software requirements for MySQL, as hardware limitations may have a significant impact on performance.

Minimal MySQL database server hardware requirements (for versions 5.7 - 8.0):

• 1Ghz processor
• 512MB RAM
• Hard disk space depending on the size of the database

It also stands to mention that it is better to use the most current official version of MySQL if possible.

Memory, disk, and CPU usage optimization

At the hardware level, you can take a bunch of actions to improve hardware and software resources.

Disk space
If you're using a traditional hard disk drive (HDD) and looking for a performance enhancement, you should consider upgrading to SSD. The official MySQL documentation does not explicitly denote the disk space or memory settings required for running MySQL server efficiently as they primarily depend on the size of the potential database or databases. However, it would be a good idea to monitor your disk performance, using the sar and iostat system performance tools, for example. If disk usage is significantly higher than the usage of other resources, you should definitely add more storage or upgrade to faster one.

RAM
Lack of memory can seriously affect database performance as well. It may seem trite, but if your server is regularly running low on memory and RAM Disk performance is not satisfying, it is worth adding more memory. When you run out of RAM, MySQL server caches physical memory which slows down performance. Thus, MySQL memory optimization is extremely important.

CPU
MySQL CPU usage optimization should start with careful analysis of the MySQL processes taking place on your machine and the percentage of processor usage they require. CPU is not cheap to upgrade, however, if it is a bottleneck, an upgrade will be necessary.

Internet connection
Network is a crucial part of MySQL infrastructure and it is important to trace and analyze network traffic to make sure you have sufficient resources to manage your workloads. Make sure you have a good and stable Internet connection for your MySQL server running properly.

Tools for software performance tuning

As we have already mentioned, you can optimize MySQL performance at the hardware and software levels. Let's now look at MySQL software performance tuning.

MySQL performance tuning in terms of software involves configuring MySQL server options, increasing the performance of MySQL queries, tuning MySQL indexes, switching to the MySQL InnoDB storage engine, etc. Let's consider all these in detail.

MySQL index usage for performance

Proper indexing for performance enhancement is not easy and requires a certain level of expertise, yet it is one of the best performance improvements you can make to your database.

MySQL uses indexes as a book index or roadmap to quickly find values for a given query. Without indexes, MySQL will scan the entire table row by row to find the relevant data. Thus, index optimization is aimed at speeding up data retrieval. Indexes are not visible to users and contain information about where the actual data is stored. It is also worth noting that MySQL index length for InnoDB tables has limits depending on the row format.

MySQL indexes are extremely useful for large datasets and index tuning is the right thing to do if your database is growing quickly. Indexes are particularly beneficial for the following operations: finding the rows matching a WHERE clause, retrieving data with JOINs, data sorting and grouping with the help of ORDER BY and GROUP BY.

So why not then insert as many indexes as you can? That would be a bad idea—unnecessary indexes occupy space and waste time of the system not to mention that they also add cost to queries as indexes need to be updated. So you have to find the right balance to achieve the optimal MySQL index usage.

Improve performance with InnoDB

One of the first tuning tips for those having a heavy load on their database would be to try switching to InnoDB from the MyISAM storage engine. Having a clustered index, with data in pages and consecutive physical blocks, InnoDB has better performance for large volumes of data as compared to MyISAM.

InnoDB also boasts a rich set of variables and advanced settings that can be configured to improve MySQL performance even further. InnoDB performance settings are more extensive and thus there are more ways to tune InnoDB for higher performance as against tuning MyISAM.

MySQL query optimization

Now let's have a look at how to optimize MySQL query for better performance and speed. For those who want to enhance MySQL queries, it would be a good idea to follow the following optimization techniques.

Add indexes to columns used in WHERE, ORDER BY, and GROUP BY clauses
In this way, you will increase the performance of MySQL query as MySQL server will fetch results from a database significantly faster.

Specify the necessary columns in the SELECT statements
Try to avoid using the SELECT * FROM as it retrieves all the columns of the table and thus causes the additional load on the server and slows down its performance. Make it a rule to always specify the columns in the SELECT statements.

Use DISTINCT and UNION sparingly
Another good tip for query tuning is to use DISTINCT and UNION operators only when necessary as the queries with them lead to server overhead and generally increase the response time. Consider replacing UNION with UNION ALL and DISTINCT with GROUP BY to bring more efficiency to the process.

Avoid using wildcards at the beginning of LIKE patterns
MySQL queries with LIKE operators often lead to the server performance downturn so they should be used carefully. MySQL can not use indexes when the LIKE pattern starts with a wildcard, for example, '%xyz', and performs a full table scan in this case. You should bear this in mind when optimizing MySQL queries and try using 'xyz%' instead whenever possible.

Use INNER JOINs instead of OUTER JOINs
Use OUTER JOIN only when necessary. MySQL does much more work fetching the results for OUTER JOINs as compared to INNER JOINs. We recommend you to check the performance of your JOIN queries and in case it is not satisfying—start converting your OUTER JOINs into INNER JOINs when possible. MySQL JOINs optimization can lead to dramatic performance improvement.

Tune server options to boost performance

Now let's focus on how to optimize MySQL server options in terms of performance tuning. For this, you will need to adjust the configuration file (my.cnf/my.ini).

innodb_buffer_pool_size
This parameter specifies the amount of memory allocated by MySQL to the InnoDB buffer pool. The recommended value for this parameter is 70-80% of available memory. The larger your data sets are the larger the value should be.

max_connection
This parameter defines the maximum permitted number of simultaneous client connections and has the default value of 151. In order to avoid getting the "Too many connections" error, the value can be increased. However, bear in mind that too many open connections can affect performance.

query_cache_size
This parameter sets the total amount of memory allocated to the query cache. The optimal value for it depends primarily on your working case and needs to be determined tentatively. The idea is to start very small—10MB—for example, then increase in small increments to 100-200MB. Adjusting query_cache_size, remember to enable query cache (query-cache-type ON). Note that large query cache size can result in a serious performance decrease.

innodb_io_capacity
This parameter specifies the number of I/O operations per second allowed for the tasks performed in the background and has the default value of 200. Generally, the value around 100 is suitable for average-level hard drives, while for faster and more modern storage devices higher values will be advantageous.

innodb_log_file_size
This parameter specifies the size in bytes for each MySQL redo log file in a log group and has the default value of 134,217,728 (about 128 MB). The innodb_log_files_in_group parameter in its turn specifies the number of log files in the log group and has the default value of 2. In case the innodb_log_file_size value is small for your workload and your application is write-intensive, we recommend increasing it. However, too large innodb_log_file_size will increase the crash recovery time. So you will have to find its optimal size.