Using a foreign key in MySQL InnoDB

August 11, 2010 | development | No Comments

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
CREATE TABLE `user` (
 `id` int NOT NULL,
 `name` varchar(128),
  PRIMARY KEY (`id`)
) engine=InnoDB;
CREATE TABLE `address` (
 `id` int NOT NULL,
 `user_id` int NOT NULL,
 `street` varchar(1),
  PRIMARY KEY (`id`),
  INDEX  `useridx` (`user_id`),
  CONSTRAINT `userfk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`)
) engine=InnoDB;
INSERT INTO `user` VALUES(1, 'erka');
INSERT INTO `address` VALUES(1, 1, 'some street');

If you check indexes on address table you will see all indexes you expect.

1
2
3
4
5
6
7
8
mysql> SHOW INDEX FROM `address`;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TABLE   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| address |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| address |          1 | useridx  |            1 | user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows IN SET (0.00 sec)

Now let’s play with this table and try to drop useridx index and get my mystic error.

1
2
3
mysql> ALTER TABLE `address` DROP INDEX `useridx`;
ERROR 1025 (HY000): Error ON RENAME of './test/#sql-5815_28'
TO './test/address' (errno: 150)

During my research I understood my problem. Let’s recreate address table without useridx index and look at the indexes of this table.

1
2
3
4
5
6
7
8
mysql> SHOW INDEX FROM `address`;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TABLE   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| address |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| address |          1 | userfk   |            1 | user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows IN SET (0.00 sec)

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.

Playing with HaProxy and Cacti

July 24, 2010 | development | No Comments

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 [...]

Bash Shell Memo

August 9, 2009 | development | No Comments

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 [...]