… secondary indexes in the table makes any difference: mysql> SET expand_fast_index_creation=OFF; Query OK, 0 rows affected (0.00 sec) mysql> ALTER… applicable: UNIQUE indexes in ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…not hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,…| idxitest | index | a,a_2 | PRIMARY | 4 | NULL | 2247 | Using where | +—-+————-+———-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec) MySQL …
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…index_usage’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
…) mysql> alter table a drop key c; Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> optimize… | OK | +——–+———-+———-+——————————————————————-+ 2 rows in set (4 min 5.52 sec) mysql> alter table a add key(c); Query OK, 0 rows affected… you’re running it on the Slave where it is OK table is exposed without indexes for some time. Note though nothing…
Post: Converting Character Sets
March 17, 2009 By 16 Comments
… Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE `t1` CONVERT TO CHARACTER SET utf8 COLLATE…client character sets. This is a three-step process, where one must first dump only the schema … leaving in place any FULLTEXT indexes. It also requires only a single ALTER statement for a given table….
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… which were accessed: mysql> select concat(‘alter table ‘,d.table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop index ‘),’;') stmt from…_NAME AND s.INDEX_NAME=iz.INDEX_NAME) WHERE iz.TABLE_SCHEMA IS NULL AND s.NON_UNIQUE=1 AND s.INDEX_NAME!=’PRIMARY…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
… index, a table scan is performed) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE test_table ADD INDEX (id); — 5 Tickets Used Query OK, 3 rows…: 0 Warnings: 0 mysql> DELETE FROM child WHERE 1; — 6 Tickets Used Query OK, 3 rows affected (0.02 sec) mysql> ALTER TABLE `child…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… I found out ALTERing Innodb tables may take a lot of time. Fast Index creation will allow Innodb to build indexes by sorting… tables by separate phase of building Indexes ? Will be UNIQUE indexes built by sorting in case of ALTER TABLE (the big Gotcha for… ? Yes indeed there is patch out where already which may come in MySQL 5.2 or even MySQL 5.1. I surely would…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
… more queries: mysql> select count(*) from fact where i explain select count(*) from fact where i select count(val) from fact where i explain…(val) from fact where i select count(val2) from fact where i explain select count(val2) from fact where i alter table fact drop… affected (37.15 sec) Records: 7340032 Duplicates: 0 Warnings: 0 mysql> select count(val) from fact where i
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…where there’s a /var/lib/mysql and an /etc/my.cnf, and the server is installed in /customsoftware/mysql…| 216162991863 | 7796961 | | Com_admin_commands | 255868807 | 11893 | | Com_alter_db | 0 | 0 | …snip This … one of these factors (schema, queries, indexing) in isolation, because they are tightly…
