329 cards
The primary goal of MySQL optimization is to reduce query response times and resource consumption while improving the database's ability to scale with growing workload. Optimization is not a single activity but a practice that spans several interrelated areas: schema design, indexing strategy, query construction, serve...
The EXPLAIN command is the primary tool for inspecting how MySQL plans to execute a SELECT query. Its output reveals the table access order, the join type used for each table, which indexes (if any) the optimizer chose, and the estimated number of rows examined at each step. By comparing plans before and after a change...
An index is a data structure that lets MySQL locate rows quickly based on column values, accelerating lookups, range filters, joins, and sorted access. By narrowing the rows MySQL must examine, an index can transform a multi-second scan into a millisecond lookup. However, indexes are not free: every insert, update, and...
Good schema design begins with choosing appropriate data types for each column. Smaller and simpler types consume less storage, fit more rows per page, improve cache hit rates, and compare more efficiently. For identifier columns, numeric types like INT and BIGINT are almost always preferable to VARCHAR because they ar...
The way a query is written often matters more than the configuration surrounding it. SELECT * is a common performance anti-pattern because it reads every column, preventing index-only scans and transferring unnecessary data across the network. Selecting only the columns actually needed allows the query to use a coverin...
Because InnoDB is MySQL's default storage engine, its internals strongly shape overall performance. The InnoDB buffer pool caches data and index pages in memory, so innodb_buffer_pool_size is one of the most important configuration variables: a larger buffer pool allows more of the working set to be served from RAM, dr...
InnoDB uses Multi-Version Concurrency Control (MVCC) to allow readers to see a consistent snapshot without blocking writers, which dramatically improves concurrency compared to simple table-level locking. To support this, InnoDB retains old row versions in the undo log until no transaction needs them anymore. Long-runn...
Effective optimization depends on good observability. The slow query log, enabled via slow_query_log and thresholded by long_query_time, captures queries that exceed runtime budgets; log_queries_not_using_indexes can additionally flag queries that scan without index use, and log_slow_admin_statements extends coverage t...