Search Results
https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-diskio.html
If you follow best practices for database design and tuning techniques for SQL operations, but your database is still slow due to heavy disk I/O activity, consider these disk I/O optimizations. Store system tablespace files on Fusion-io devices You ...If the Unix top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably ...
https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-many-tables.html
If you have configured non-persistent optimizer statistics (a non-default configuration), InnoDB computes index cardinality values for a table the first time that table is accessed after startup, instead of storing such values in the table. This ...
https://dev.mysql.com/doc/refman/8.4/en/distinct-optimization.html
Thus, for more details on the optimization possibilities for DISTINCT queries, see Section 10.2.1.17, “GROUP BY Optimization”. DISTINCT combined with ORDER BY needs a temporary table in many cases. Because DISTINCT may use GROUP BY, learn how ...
https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-queries.html
The optimizer can better determine which index is most effective to use for a query, when it knows whether each column contains NULL values. You can optimize single-query transactions for InnoDB tables, using the technique in Section 10.5.3, ... To ...
https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-storage-layout.html
Once your data reaches a stable size, or a growing table has increased by tens or some hundreds of megabytes, consider using the OPTIMIZE TABLE statement to reorganize the table and compact any wasted space. OPTIMIZE TABLE copies the data part of ...The reorganized tables require less disk I/O to perform full table ...
https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-transaction-management.html
To optimize InnoDB transaction processing, find the ideal balance between the performance overhead of transactional features and the workload of your server. Alternatively, for transactions that consist only of a single SELECT statement, turning on ...For example, an application might encounter performance issues if it commits thousands of times per second, and different performance issues if it commits only every 2-3 ...
https://dev.mysql.com/doc/refman/8.4/en/optimizing-queries-myisam.html
Some general tips for speeding up queries on MyISAM tables: To help MySQL better optimize queries, use ANALYZE TABLE or run myisamchk --analyze on a table after it has been loaded with data. Another possibility is to run OPTIMIZE TABLE to ...This ...
https://dev.mysql.com/doc/refman/8.4/en/update-optimization.html
An update statement is optimized like a SELECT query with the additional overhead of a write. If you do this often, it is very important to use OPTIMIZE TABLE occasionally. The speed of the write depends on the amount of data being updated and the ...
https://dev.mysql.com/doc/internals/en/preventing-use-of-optimizer-trace.html
If for some reason, as DBA of a MySQL Server, you wish to prevent all users from seeing traces of their queries, start the server with these options: --maximum-optimizer-trace-max-mem-size=0 --optimizer-trace-max-mem-size=0 This sets the maximum ...
https://dev.mysql.com/doc/refman/8.4/en/information-schema-optimization.html
For INFORMATION_SCHEMA tables implemented as views on data dictionary tables, indexes on the underlying data dictionary tables permit the optimizer to construct efficient query execution plans. Applications that monitor databases may make frequent ...