June 19, 2013

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

… start to get information in the INFORMATION_SCHEMA.INDEX_STATISTICS table.  This data collection does add some overhead to your running server, but… generate the precise ALTER TABLE statements to drop these indexes, an exercise left to the reader. :) Estimating the size of these indexes But, what…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Using innodb_sys_tables and innodb_sys_indexes

June 15, 2012 By Leave a Comment

…_schema tables we added in Percona Server. I was doing simple ALTER TABLE such as: “alter table sbtest add z …sec) mysql> select * from innodb_sys_indexes where table_id=13; +———-+———+———-+——+———-+———+——-+ | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_…

Filed Under: Insight for DBAs, MySQL

Post: Full table scan vs full index scan performance

November 23, 2012 By 15 Comments

… take the employees database, and slightly modify the employees tables: mysql> ALTER TABLE employees ADD INDEX idx_first (first_name),ENGINE=InnoDB; And then let… if we know that the table will always be in memory, we should add the FORCE INDEX hint to ensure optimal response time… a non covering index, the different between a full table scan and an execution plan based on a full index scan is basically…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: ,

Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)

June 19, 2012 By 2 Comments

… (courtesy of Alexey Kopytov), using 1Mil rows Sysbench table. Buffer Length | alter table sbtest add key(c) 1MB 34 sec 8MB 26 sec 100MB… in this table is using “fast_index_creation=0″ which allows to disable fast index creation in Percona Server and force complete table to… to do alter table. You may be wondering why in this case table rebuild is so close in performance to building index by sort…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Quick tip: how to convert tables to InnoDB

July 30, 2008 By 5 Comments

… don’t want to touch. mk-find –engine MyISAM –exec “ALTER TABLE %D.%N ENGINE=INNODB” –print Here’s a bonus tip… index to them — but not to the ones named friends_123_456_user. mk-find –tblregex ‘^user_\d+_\d+_friends$’ –exec ‘ALTER TABLE %D.%N ADD KEY(site_id)’ Boy, is that a lot easier than adding indexes to 90k tables by hand!

Filed Under: Insight for DBAs Tagged With:

Post: How well does your table fits in innodb buffer pool ?

December 9, 2010 By 12 Comments

… Percona Server however adds number of tables to Information Schema…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…affected by batch jobs, alter tables, optimize table etc – the lasting…

Filed Under: Insight for DBAs, MySQL Tagged With: , , , ,

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

… safe operation, unless index length increases dramatically queries which can use index can also use prefix of the new index are they ? It… hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK, 0 rows affected… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———-+——-+—————+———+———+——+——+————-+ | 1 | SIMPLE | idxitest | index | a | PRIMARY | 4…

Filed Under: Insight for DBAs, MySQL Tagged With: ,

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…

Filed Under: Insight for DBAs, MySQL

Post: Redundant index is not always bad

August 28, 2007 By 7 Comments

… covering index, which store all needed columns in the index, and there is no need to read row data from the table. So – we can extend index `state_id_idx` (`state_id`) by two columns: ALTER TABLE userinfo DROP KEY state_id, ADD KEY…

Filed Under: Benchmarks, Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… full-text index on it, and then I create one, the following warning is generated: mysql> alter table dir_test_innodb ADD FULLTEXT KEY…> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD FULLTEXT KEY (details); ERROR 1795 (HY000): InnoDB presently supports one FULLTEXT index creation…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,
« Older Posts
Newer Posts »