For transaction blocking, the first line of defense is to speed up the query. Though this does not completely eliminate the possibly of block/delay/deadlock, it will often make the situation more tolerable.
Re the LIMIT 1 -- Is there only 1? If so, why not do a JOIN. Or might there be more than one? If so, which one? That is, don't you need an ORDER BY?
If you are using MyISAM, don't.
If you found that DATETIME is 8 bytes, you are using an old version of MySQL; it takes only 5 bytes now. Upgrade.
"Fixed" is slightly important for MyISAM; rarely important for InnoDB. So don't worry. In particular, don't worry unless you have a billion rows.
Take a look at the date on the article you linked to. It's from 2006, which is fifteen years ago as we write this. The latest version of MySQL at that time was 5.0.30.
The key_buffer_size configuration option only applies to MyISAM tables. It's the size of RAM allocated for caching MyISAM indexes. InnoDB tables do not use the key buffer.
I notice one of your ...
For InnoDB Tables
Helpful Notes About NULLs:
For the third row, I inserted NULLs in FIELD2 and FIELD3. Therefore in the Field Start Offsets the top bit is on for these fields (the values are 94 hexadecimal, 94 hexadecimal, instead of 14 hexadecimal, 14 hexadecimal). And the row is shorter because the NULLs take no space.
Do not delete the files via the file system unless you enjoy seeing messages like this from the database:
InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and ....
When the deadlock occurs at query 3, the deadlock may involve query 1 or 2. You must start over with the START TRANSACTION and replay all the queries.
If any of those are SELECTs and the result of the SELECT controls what will be subsequently UPDATEd, DELETEd, etc, then do you have FOR UPDATE on the SELECT?
You must check for errors after each and every ...
Why they can't be separated
With ENGINE=InnoDB, the data's BTree (sorted by the PRIMARY KEY) and all secondary indexes (again BTrees) are stored in the same 'tablespace'.
A tablespace is implemented as a file. Therefore, it is not possible to separate them. (MyISAM is different, but let's not go there.)
A tablespace is manifested in one of 3 ways, none of ...
From the documentation:
INPLACE: Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.
The table is being rebuilt because the order of the columns is changing. As you said in your ...
I've read through a lot of the MySQL documentation online but can't find the relevant page.
The most relevant pages are:
Creating InnoDB Tables
InnoDB tables are created in file-per-table tablespaces by default. To create an InnoDB table in the InnoDB system tablespace, disable the innodb_file_per_table variable before creating the table. To create an ...
Rather than change a pair of tables to InnoDB, it would be better for every table in the database to be converted as InnoDB is a much better storage engine. That said, MyISAM is workable if you don't mind babysitting it every once in a while.
Do I need to clear cache or optimize table in order to see the effects?
⇢ The database engine will clear the cache on ...
It is possible to find the number of rows looked at in the tables for a single query. These count rows of index or data, not counted separately, for your connection. If no other queries are running, total counts are possible.
SHOW SESSION STATUS LIKE 'Handler%';
Here is a discussion of using that to help with optimization: http://mysql.rjweb.org/...
I beg to differ. A high max_connections can cause a problem. Here's the general picture:
When there are more than a few dozen database connections actively trying to perform SELECTs, etc, they contend for various resources -- mutexes, cpu, I/O, and various 'shared' things in the database server.
At that point, mysqld tries to be fair, giving each ...
I tried different methods and read many articles. The standard way to do so is
ALTER TABLE t1 KEY_BLOCK_SIZE=0 `PAGE_COMPRESSED`='ON';
However, this will leave KEY_BLOCK_SIZE on all keys.
Some say this is just the SHOW CREATE TABLE display, as KEY_BLOCK_SIZE=8 standards for the largest block size for the keys, but the value is exactly that of the COMPRESSED ...
common_schema is a tool that someone added to your server? Sounds like it has not been updated to utf8mb4.
CHARACTER SET utf8 (aka utf8mb3) is old; utf8mb4 is new. The latter can handle Emoji and more of the Chinese character set, plus some less common languages.
If you don't know anything about common_schema, ignore the warning.
In your tables, consider ...
Those two DELETEs are each doing a full table scan looking for certain rows to delete. Redundant. One will be automatically killed (by InnoDB); the other will to the task assigned.
Just looking at those Deletes, I see two issues --
Potentially inefficient way to delete rows. If this is a big table, let's talk about ways to walk through it in chunks.