…TABLE. P.S I do not know why this was not done when support for creating index…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…Records: 245760 Duplicates: 0 Warnings: 0 mysql> show index from antest_innodb; +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
…index are they ? It turns there are special cases when this is not the case. CREATE TABLE…mysql> alter table idxitest drop key a,add key(a,b); Query OK, 0 rows affected (24.84 sec) Records: 0 Duplicates…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…in a running transaction, two identical statements get different values, because some other transaction has modified the table…no need to create locks to prevent … checking or duplicate-key checking. …MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…in decent shape but if it is 5000 you might need to rethink your indexing. Also note – number of records…index can’t be used. Sometimes it means breaking normalization and duplicating column(s) you’re going to use in ORDER BY in other table…table: mysql…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…mysql test — 4. mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE TABLE `huge_table`\G *************************** 1. row *************************** Table: huge_table Create Table: CREATE TABLE `huge_table…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 45 Comments
…in set (0.00 sec) As this is MYISAM table MySQL has cached number of rows in this table…table fact drop key i, add key(i,val); Query OK, 7340032 rows affected (37.15 sec) Records: 7340032 Duplicates: 0 Warnings: 0 mysql…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…mysql> CREATE TABLE test_table ( -> id int -> ) ENGINE=InnoDB; — 0 Tickets Used Query OK, 0 rows affected (0.36 sec) mysql> INSERT INTO test_table…mysql> ALTER TABLE test_table ADD INDEX (id); — 5 Tickets Used Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates… (2 * rows in the table) – 1 As…
Comment: What does Using filesort mean in MySQL?
March 6, 2009 By 27 Comments
…, then the index you’re creating won’t be used for the optimization. Here’s an example with another schema: mysql> create table t1 (var… in set (0.00 sec) mysql> alter table t1 add index(id); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql…
