…, for example: mysql> create table t1(i int, j int); Query OK, 0 rows affected (0.01 sec) mysql> alter table t1 add key… index name ? MySQL will happily create (and maintain) as many duplicate keys as you like without even giving you a warning: mysql> alter table… if you would specify index name MySQL will complain if you try to create index with same name again: mysql> alter table t1 add key…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table…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: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… the fast index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX. Peter… indexes in ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables containing…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
… reading table with no indexes and bringing box down. You can also use this trick for ALTER TABLE which requires table rebuild. Dropping all indexes; doing ALTER and when adding them back can be a lot faster than straight ALTER TABLE. P.S I do not know why this was not done when support for creating index by sorting was…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
… system is simple: create an Innodb table make a copy of the .frm file do an ALTER TABLE to add an index then copy the… test table that the extra index in Innodb could be removed by doing: ALTER TABLE table_name ENGINE=Innodb; This, of course, rebuilds the whole table based on the .frm table definition and removes the existing index in…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
… of the new index are they ? It turns there are special cases when this is not the case. CREATE TABLE `idxitest` ( `id` int… should not hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK… what suppose to work but it well often does not: CREATE TABLE `idxitest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a…
Post: Impossible - possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
…of tables with –export options 3) create table on destination server with exactly the same CREATE TABLE statement …alter table IMPORTEDTABLE discard tablespace; 5) copy .exp and .ibd files to destination server 6) run alter table… on root leafs of secondary indexes also in system tablespace. And …
Post: Copying InnoDB tables between servers
July 31, 2009 By 10 Comments
… CHARSET=latin1 Next step is to remove created .ibd file, it’s done by command: alter table order_line discard tablespace; And now… MysqlDataDir/testimport dir. When it’s done, final import command: alter table order_line import tablespace; now some magic happens and you… import of testexport/order_line is started. InnoDB: import: 2 indexes are detected. InnoDB: Progress in %: 1 2 3 4 5…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 11 Comments
… innodb_index_stats.table_name = innodb_sys_tables.name AND innodb_sys_indexes.name = innodb_index_stats.index_name AND innodb_index_stats.table_schema = innodb_sys_tables.SCHEMA… what is the percentage of index fits in memory (fit_pct) For illustration purposes I’ve created one table with partitions to show… well as how buffer pool is affected by batch jobs, alter tables, optimize table etc – the lasting impact these may have on system…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…index_usage \ –create-save-results-database After that finishes, the ‘index_usage’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes | | queries | | tables…
