… space id 19 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 72C lock_mode… the gap locks except for foreign-key constraint checking or duplicate-key checking. The most important difference between these two options…-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and the…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… index will also be able to use longer index. Unlike with duplicate indexes, there are however cases when redundant indexes are helpful – typically if longer index… currently no tool in MySQL distribution which will help you to check your schema for redundant and duplicate indexes. If you know one…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… you do not specify index name MySQL will name index by the first column of index created, if there is such index already it will… (42000): Duplicate key name ‘idx_i’ Adding indexes without name specified is very common reason why systems tend to get duplicate indexes. BTW make sure you check yours with mk-duplicate…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… can “compress” the table in the database by removing the duplicates: mysql> create table ex2 as select val, count(*) from ex1 group… OK, 9 rows affected (19.51 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> select * from ex2; +—–+———-+ | val | count(*) | +—–+———-+ | -10 | 1 | | -3… is no index on this table. mysql> delete from data where val=16; Query OK, 1 row affected (3.14 sec) mysql> select…
Comment: Duplicate indexes and redundant indexes
August 21, 2006 By 38 Comments
MySQL Index Analysis Tool… Back in January I posted a simple MySQL duplicate index finder tool. Because I read requests for such a tool on the MySQL Performance Blog I decided to open a new project on Google’s code hosting service as well as a new blog to track it. S…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… Duplicates: 0 Warnings: 0 mysql> SHOW PROFILE; +——————————+————+ | Status | Duration | +——————————+————+ . . . | copy to tmp table | 184.694432 | . . . +——————————+————+ 18 rows in set (0.00 sec) mysql…) Records: 4194304 Duplicates: 0 Warnings: 0 mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
….29 sec) Records: 245760 Duplicates: 0 Warnings: 0 mysql> show index from antest_innodb; +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…, 0 rows affected (24.84 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from idxitest where a=5 and b… FORCE INDEX(a) to force MySQL optimizer using right plan. These results mean you should be very careful applying index changes from mk-duplicate-key-checker key checker when it comes to redundant indexes. If you have query…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…=/var/db/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql.sock consult… the previous issue’s findings, so that we don’t duplicate efforts from the earlier optimization. Back to the output of… checking for default users in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and so on. As with settings…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… as for MyISAM tables by separate phase of building Indexes ? Will be UNIQUE indexes built by sorting in case of ALTER TABLE… probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible to… patch out where already which may come in MySQL 5.2 or even MySQL 5.1. I surely would like to see…
