MySQL is very smart. Even it is so smart that I think sometimes I am stupid at all. Yesterday I made a research for our application. We have several huge tables and one of them is very interesting because it has 300 mb data and (wow) 600 mb index space usages. My first thought was to drop several indexes and find a better solution. But when I tried to drop them I got a mystic error: ERROR 1025 (HY000): Error on rename of ‘/path/to/table1′ to ‘/path/to/table2′.
For your better understanding of my situation I’ve simulated my problem. Let’s say I have two tables user and address. The second table has useridx index on user_id field and foreign key on the same field to user table. It looks very clear and simple.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
CREATETABLE`user`( `id` int NOTNULL, `name` varchar(128), PRIMARYKEY(`id`) ) engine=InnoDB; CREATETABLE`address`( `id` int NOTNULL, `user_id` int NOTNULL, `street` varchar(1), PRIMARYKEY(`id`), INDEX`useridx`(`user_id`),
CONSTRAINT `userfk`FOREIGNKEY(`user_id`)REFERENCES`user`(`id`) ) engine=InnoDB; INSERTINTO`user`VALUES(1,'erka'); INSERTINTO`address`VALUES(1,1,'some street');
If you check indexes on address table you will see all indexes you expect.
Oops… It looks like MySQL created an index for our foreign key automatically. So perhaps MySQL needs an index for InnoDB foreign key but it can reuse already existed one. In this situation you can’t drop that index (useridx in my case) because it is internally used by the foreign key (userfk in my case). Of course MySQL does a great job here and don’t waste a server memory and a server disk space, but you can be disappointed without knowing this behaviour. Now it’s clear that I have to drop userfk foreign key before dropping useridx index.
When I push my application to production I often want to know more about it. Google Analytics helps me to learn more about my users. Sometimes it’s interesting for me but not as much as for my product owner and managers. I often want to know more about our software, boxes and processes. Cacti and [...]
I miss or forget these commands very often. So I decided to write a post. If you know some other helpful commands or key combinations please let me know about them. At the beginning I want to write some bash navigation commands. First one should be well known by you. It’s a quick search of [...]