InnoDB will notice circular dependencies and return an error instantly, to prevent deadlocks.
In InnoDB, secondary indexes contain primary key values. So if your primary key is large, other indexes will also be large.
Very active swapping can cause the entire operating system to become unresponsive, to the point that you can’t even log in and kill the MySQL process. We recommend you run your databases without using swap space at all.
If you set a variable’s global value whole the server is running, the values for the current session and any other existing sessions are not affected. Keep this in kind if your clients rely on persistent connections. This is because the session values are initialized from the global value when the connections are created.
Mysql8.0 introduced a new feature called persisted system variables which allows you to set the value once for runtime and MySQL will write this setting out to disk, enabling it to be used at the next start.
In MySQL8.0, a new configuration option, innodb_dedicated_server, was introduced. This option examines the available memory on the server abd configures four additional variables:
innodb_buffer_pool_size
inmodb_log_file_size
innodb_log_files_in_group
innodb_flush_method
The InnoDB buffer pool needs more memory than anything else, as it is generally the most important variable for performance.
It is best to specify columns as NOT NULL unless you intend to store NULL in them. It’s harder for MySQL to optimize queries that refer to nullable columns because they make indexes, index statistics and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL.
Width for integer types is meaningless for most applications; it does not restrict the legal range of values but simply specifies the number of characters MySQL’s interactive tools, like cli. For storage and computational purposes, INT(1) is identical to INT(20).
MySQL’s storage engine API works by copying rows between the server and the storage engine in a row buffer format; the server then decodes the buffer into columns. It can be costly to turn the row buffer into the row data structure with the decoded columns. InnoDB’s row format always requires conversion. The cost of this conversion depends on the number of columns. We discovered that this can become expensive when we investigated an issue with high CPU consumption for a customer with extremely wide tables (hundreds of columns), even though only a few columns were actually used. If you are planning for hundreds of columns, be aware that the server’s performance characteristics will be a bit different.
Changes to your primary key, changes to your charsets, turning on per-table encryption, and adding or removing foreign keys are all examples of schema changes you still cannot do natively with an INPLACE alter.
If you index more than one column, the column order is very important because MySQL can only search efficiently on the leftmost prefix of the index.
Creating an index on two columns is not the same as creating two separated single-column indexes.
