{"dataset":{"id":"00000000-0000-0000-0000-000000000028","slug":"mysql-best-practices","name":"MySQL best practices","description":"Source-linked guidance on MySQL 8.4 fundamentals: indexes, EXPLAIN execution plans, the InnoDB engine, constraint handling, and SELECT optimization. Scoped to MySQL 8.4; not a general SQL corpus.","visibility":"public","version":1,"documentCount":5,"publisher":"Koskamo editorial","license":"Original summaries; linked sources retain their own terms.","reviewedAt":"2026-10-04T00:00:00.000Z","readOnly":true,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"},"total":5,"documents":[{"title":"Optimizing SELECT statements","sourceUrl":"https://dev.mysql.com/doc/refman/8.4/en/select-optimization.html","text":"SELECT statements perform all the lookup operations in the database, so tuning them is a top priority, and the same techniques apply to CREATE TABLE...AS SELECT, INSERT INTO...SELECT, and WHERE clauses in DELETE statements. To make a slow SELECT ... WHERE query faster, the first thing to check is whether you can add an index: index the columns used in the WHERE clause to speed evaluation, filtering, and row retrieval, but build a small set of indexes that speeds many related queries. Indexes matter most for queries that reference different tables through joins and foreign keys, and EXPLAIN shows which indexes a SELECT uses. Isolate and tune any part of the query, such as a function call, that takes excessive time, and minimize full table scans on big tables. Run ANALYZE TABLE periodically so the optimizer has current statistics for efficient plans, learn the tuning techniques specific to each table's storage engine, and size memory areas such as the InnoDB buffer pool. If a problem persists, read the EXPLAIN plan and adjust indexes, WHERE clauses, and join clauses. Scope: MySQL 8.4. Reviewed October 4, 2026.","id":"mysql-best-practices:Optimizing SELECT statements","version":1,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"},{"title":"Constraint handling and SQL modes","sourceUrl":"https://dev.mysql.com/doc/refman/8.4/en/constraints.html","text":"MySQL works with both transactional tables that permit rollback and nontransactional tables that do not, so constraint handling differs from other DBMSs. The server's philosophy is to raise an error for anything it can detect while parsing a statement and to recover as well as possible from errors that occur during execution. When an execution error occurs, the options are to stop the statement in the middle or to recover and continue; by default the server continues, which means it may coerce an invalid value to the closest valid value. SQL mode options give greater control over handling bad data values and over whether execution continues or aborts on error, letting you configure the server to reject improper input like more traditional DBMSs. The SQL mode can be set globally at server startup for all clients, or by individual clients at runtime. Constraint types covered include PRIMARY KEY and UNIQUE index constraints, FOREIGN KEY constraints, and ENUM and SET constraints. Choosing a strict SQL mode so bad data is rejected rather than coerced is Koskamo editorial guidance. Scope: MySQL 8.4. Reviewed October 4, 2026.","id":"mysql-best-practices:Constraint handling and SQL modes","version":1,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"},{"title":"InnoDB: the default engine with ACID transactions","sourceUrl":"https://dev.mysql.com/doc/refman/8.4/en/innodb-introduction.html","text":"InnoDB is a general-purpose storage engine balancing high reliability and high performance, and in MySQL 8.4 it is the default storage engine: a CREATE TABLE statement without an ENGINE clause creates an InnoDB table unless a different default is configured. Its DML operations follow the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data. Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance, and InnoDB supports MVCC with row locking granularity and a 64TB storage limit. Every InnoDB table has a primary key index called the clustered index that arranges data on disk to minimize I/O for primary key lookups, so choose primary keys with lookups in mind. InnoDB also supports FOREIGN KEY constraints, checking inserts, updates, and deletes so they cannot produce inconsistencies across related tables. Keeping tables on InnoDB unless a workload has a documented need for another engine is Koskamo editorial guidance. Scope: MySQL 8.4. Reviewed October 4, 2026.","id":"mysql-best-practices:InnoDB: the default engine with ACID transactions","version":1,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"},{"title":"Query execution plans with EXPLAIN","sourceUrl":"https://dev.mysql.com/doc/refman/8.4/en/explain.html","text":"EXPLAIN shows how MySQL would execute a statement, and it works with SELECT, DELETE, INSERT, REPLACE, UPDATE, and TABLE statements, displaying optimizer information about the execution plan including how tables are joined and in which order. DESCRIBE and EXPLAIN are synonyms to the parser, but in practice DESCRIBE is used for table structure and EXPLAIN for execution plans. The FORMAT option selects the output: TRADITIONAL presents tabular output, JSON displays the plan as JSON, and TREE gives tree-like output with more precise descriptions; TREE is the only format that shows hash join usage and is always used for EXPLAIN ANALYZE. In MySQL 8.4 the default format when no FORMAT option is given is determined by the explain_format system variable. EXPLAIN FORMAT=JSON supports an INTO clause that saves the JSON plan into a user variable, and EXPLAIN FOR CONNECTION connection_id reports the plan for a statement executing in another connection. Scope: MySQL 8.4. Reviewed October 4, 2026.","id":"mysql-best-practices:Query execution plans with EXPLAIN","version":1,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"},{"title":"Indexes for faster SELECT lookups","sourceUrl":"https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html","text":"The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns tested in the query. Index entries act like pointers to the table rows, letting the query quickly determine which rows match a WHERE clause condition and retrieve the other column values for those rows, and all MySQL data types can be indexed. Resist indexing every column that appears in a query: unnecessary indexes waste disk space and waste optimizer time while MySQL decides which index to use, and they add cost to inserts, updates, and deletes because every index must be maintained on each write. The goal is the right balance — the optimal set of indexes that keeps queries fast without over-taxing writes. The optimization chapter drills into primary key optimization, multiple-column indexes, verifying index usage, and invisible and descending indexes for finer control over what the optimizer can see. Scope: MySQL 8.4. Reviewed October 4, 2026.","id":"mysql-best-practices:Indexes for faster SELECT lookups","version":1,"createdAt":"2026-10-04T00:00:00.000Z","updatedAt":"2026-10-04T00:00:00.000Z"}]}