4
Access to a row using a non-clustered index, which idx_name is, requires extra random I/O: b-tree lookup finds the clustered (primary key) index value, then you need to go and fetch the actual row from the clustered index.
The alternative is a sequential scan of the clustered index itself, which does not incur that extra I/O cost and is also simply more ...
3
There are three differences as far as I can tell (according to their documentation):
Case-mappings (for LOWER() / UPPER() functions):
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html#charset-unicode-sets-uca
The LOWER() and UPPER() functions perform case folding according to the collation of their argument.
The difference between the two ...
1
Make the CHARACTER SET and COLLATION of transaction_id the same across the tables. That will switch it so that f is looked at first.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c4ed17af818aab569b37178b91d3c2b6
1
That's what mysqldump does by default, or at least with some option. Look at the output to confirm.
It will build INSERT statements will hundreds or thousands of rows in each. This is optimal. (A single INSERT with 10M rows is probably not possible.)
1
Right From the MySQL 8.0 Docs
It is possible for UPDATE and DELETE statements to produce an error in safe-updates mode even with a key specified in the WHERE clause, if the optimizer decides not to use the index on the key column:
Range access on the index cannot be used if memory usage exceeds that permitted by the range_optimizer_max_mem_size system ...
1
Yes, you can make MySQL not use as much swap by making it use huge memory pages. Huge pages are unswappable.
In my.cnf, [mysqld] section:
large-pages = 1
Additional OS level configuration is required. For example:
groupadd -g 630 hugetlb
usermod -G hugetlb mysql
sysctl -w vm.nr_hugepages = 20480
sysctl -w vm.hugetlb_shm_group = 630
You may need to increase ...
1
It has to scan the whole index. Look at the output of:
SHOW INDEX FROM user_actions;
You will see the approximate cardinality of each index. The approximation is based on a few random dives into the index, not an exhaustively checked exact number. The approximation is sufficient for balancing.
Only top voted, non community-wiki answers of a minimum length are eligible
