… index name ? MySQL will happily create (and maintain) as many duplicate keys as you like without even giving you a warning: mysql> alter table t1 add… 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 idx_i…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
…: [ERROR] Table database_name/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL This… simple: create an Innodb table make a copy of the .frm file do an ALTER TABLE to add an index then copy the old .frm file back into place re-open the table (Might need a FLUSH TABLES or…
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…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: 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…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… 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 TABLE t ADD KEY (c), ADD…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
… 2 rows in set (4 min 5.52 sec) mysql> alter table a add key(c); Query OK, 0 rows affected (5 …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…
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…: 0 mysql> DELETE FROM child WHERE 1; — 6 Tickets Used Query OK, 3 rows affected (0.02 sec) mysql> ALTER TABLE `child` ADD PRIMARY KEY…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…_sent | 216162991863 | 7796961 | | Com_admin_commands | 255868807 | 11893 | | Com_alter_db | 0 | 0 | …snip This output is… It is a “garbage” query that just adds latency and makes the overall time to… users in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and so on….
Post: MySQL Slow query log in the table
May 31, 2007 By Leave a Comment
… engine and add extra indexes as you require. Doing so however may affect your performance – updating table with a lot of indexes may be… quickly without need to have indexes would be quite handy I also should tell log table implementation in MySQL is well though of in regards to avoiding trouble. For example you can’t ALTER log table while…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… you absolutely need now; then add the rest later, reloading the data… you from the downtime caused by ALTER TABLE — a typical reason to think …indexes less than half the size. In real life this technique often makes the table+index…either using MySQL 5.1′s row-based replication, or in MySQL 5.0…
