title: MariaDB/MySQL composite unique index not effective
body: <p>Situation: I've created unique composite index over table, which took some time, didn't delete duplicite records and doesn't prevent me from inserting duplicate rows.</p>
  
  <p>Anybody knows what is going on in here?</p>
  
  <p>Table structure:</p>
  
  <pre><code>&gt; DESCRIBE translations;
  +-------------+--------------+------+-----+---------+----------------+
  | Field       | Type         | Null | Key | Default | Extra          |
  +-------------+--------------+------+-----+---------+----------------+
  | id          | int(11)      | NO   | PRI | NULL    | auto_increment |
  | text        | text         | NO   |     | NULL    |                |
  | language_id | int(11)      | NO   | MUL | NULL    |                |
  | parent_id   | int(11)      | YES  | MUL | NULL    |                |
  | type        | varchar(255) | YES  | MUL | NULL    |                |
  | flag        | varchar(255) | YES  | MUL | NULL    |                |
  +-------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Index creation:</p>
  
  <pre><code>&gt; ALTER IGNORE TABLE `translations`
  ADD UNIQUE `unique_translations`
  (`language_id`, `parent_id`, `type`, `flag`);
  
  Query OK, 12225526 rows affected (4 min 51.91 sec)     
  Records: 12225526  Duplicates: 0  Warnings: 0
  </code></pre>
  
  <p>Indexes listing:</p>
  
  <pre><code>&gt; SHOW INDEXES FROM `translations`;
  +--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table        | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | translations |          0 | PRIMARY             |            1 | id          | A         |    12178547 |     NULL | NULL   |      | BTREE      |         |               |
  | translations |          0 | unique_translations |            1 | language_id | A         |        2712 |     NULL | NULL   |      | BTREE      |         |               |
  | translations |          0 | unique_translations |            2 | parent_id   | A         |     2435709 |     NULL | NULL   | YES  | BTREE      |         |               |
  | translations |          0 | unique_translations |            3 | type        | A         |     2435709 |     NULL | NULL   | YES  | BTREE      |         |               |
  | translations |          0 | unique_translations |            4 | flag        | A         |     3044636 |     NULL | NULL   | YES  | BTREE      |         |               |
  | translations |          1 | language_id_fk      |            1 | language_id | A         |          26 |     NULL | NULL   |      | BTREE      |         |               |
  | translations |          1 | parent_id_fk        |            1 | parent_id   | A         |     1522318 |     NULL | NULL   | YES  | BTREE      |         |               |
  | translations |          1 | flag                |            1 | flag        | A         |       10562 |     NULL | NULL   | YES  | BTREE      |         |               |
  | translations |          1 | type                |            1 | type        | A         |       30370 |     NULL | NULL   | YES  | BTREE      |         |               |
  +--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  </code></pre>
  
  <p>Verification of problem:</p>
  
  <pre><code>&gt; SELECT COUNT(`id`) FROM `translations`;
  +-------------+
  | COUNT(`id`) |
  +-------------+
  |  12225526   |
  +-------------+
  1 row in set (3.29 sec)
  
  &gt; SELECT * FROM `translations` ORDER BY `id` DESC LIMIT 1;
  +----------+----------------+-------------+-----------+--------------+-------------+
  | id       | text           | language_id | parent_id | type         | flag        |
  +----------+----------------+-------------+-----------+--------------+-------------+
  | 13754252 | text           |          50 |      NULL | text2        | text3       |
  +----------+----------------+-------------+-----------+--------------+-------------+
  1 row in set (0.01 sec)
  
  &gt; INSERT INTO `translations` VALUES (NULL, "text", 50, NULL, "text2", "text3");
  Query OK, 1 row affected (0.00 sec)
  
  &gt; SELECT COUNT(`id`) FROM `translations`;
  +-------------+
  | COUNT(`id`) |
  +-------------+
  |  12225527   |
  +-------------+
  1 row in set (2.19 sec)
  </code></pre>
  
  <p>Machine info:</p>
  
  <pre><code>root@precise64:~# uname -a
  Linux precise64 3.13.0-43-generic #72-Ubuntu SMP Mon Dec 8 19:35:06 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
  root@precise64:~# dpkg -l | grep -i mariadb
  ii  libmariadbclient18                  10.0.14+maria-1~precise          amd64        MariaDB database client library
  ii  mariadb-client-10.0                 10.0.14+maria-1~precise          amd64        MariaDB database client binaries
  ii  mariadb-client-core-10.0            10.0.14+maria-1~precise          amd64        MariaDB database core client binaries
  ii  mariadb-common                      10.0.14+maria-1~precise          all          MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)
  ii  mariadb-server                      10.0.14+maria-1~precise          all          MariaDB database server (metapackage depending on the latest version)
  ii  mariadb-server-10.0                 10.0.14+maria-1~precise          amd64        MariaDB database server binaries
  ii  mariadb-server-core-10.0            10.0.14+maria-1~precise          amd64        MariaDB database core server files
  ii  mysql-common                        10.0.14+maria-1~precise          all          MariaDB database common files (e.g. /etc/mysql/my.cnf)
  root@precise64:~# dpkg -l | grep -i mysql  
  ii  libdbd-mysql-perl                   4.025-1                          amd64        Perl5 database interface to the MySQL database
  ii  libmysqlclient18                    10.0.14+maria-1~precise          amd64        Virtual package to satisfy external depends
  ii  mariadb-common                      10.0.14+maria-1~precise          all          MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)
  ii  mysql-common                        10.0.14+maria-1~precise          all          MariaDB database common files (e.g. /etc/mysql/my.cnf)
  ii  php5-mysql                          5.5.9+dfsg-1ubuntu4.5            amd64        MySQL module for php5
  rc  php5-mysqlnd                        5.3.10-1ubuntu3.11               amd64        MySQL module for php5 (Native Driver)
  ii  phpmyadmin                          4:4.0.10-1                       all          MySQL web administration tool
  root@precise64:~#
  </code></pre>