Do you wonder if MySQL tells you the truth about writes to tables that have foreign key constraints? The situation is complex, and getting visibility on what is really happening can be a problem.
I found this issue intriguing and decided to share and highlight some examples.
Query Plan
Let us take this example table:
CREATE TABLE `product` ( `category` int NOT NULL, `id` int NOT NULL, `price` decimal(10,0) DEFAULT NULL, PRIMARY KEY (`category`,`id`) ) ENGINE=InnoDB;
We want to know how costly an example UPDATE against this table will be:
mysql > EXPLAIN update product set id=id+1 where id=65032158 and category=3741760\G *************************** 1. row *************************** id: 1 select_type: UPDATE table: product partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const,const rows: 1 …[Read more]