June 19, 2013

Post: Ultimate MySQL variable and status reference list

MySQL manual, especially the option and variable reference table

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

indexes; mysql> create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes.table_name as table_name, all_indexes.index_name as index_name, ROUND(all_indexes.index

Post: Why ALTER TABLE shows as two transactions in SHOW ENGINE INNODB STATUS

table `schema`.`table_name` trx id XXXX lock mode S” in SHOW ENGINE INNODB STATUS. Another is created when adding or dropping an indexTABLE not being completely crash safe in MySQL is the MySQL server manipulating FRM files. The only MySQL

Post: Troubleshooting MySQL Upgrade Performance Regressions

… and you might need to create a more complicated test which …INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the same (run SHOW INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL

Post: MySQL Upgrade Webinar Questions Followup

…expand_fast_index_creation feature, as I just wrote this option can help creating tables a lot …tables are on track to be depreciated in future MySQL versions also MyISAM tables are essentially at the state they were in MySQL…during Upgrade? This is one of the big challenges with upgrade which can’t …

Post: How Percona does a MySQL Performance Audit

Created_tmp_tables and Created_disk_tmp_tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table

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

…never been a very big fan of MyISAM; I would argue that in most…table that has never had a full-text index on it, and then I create one, the following warning is generated: mysql> alter table… mixture of lettercase, you’ll get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED …

Post: Find and remove duplicate indexes

…’s see this example: mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> show create table t\G [...] KEY `name… and with an index on (name,id) the execution plan changes to Using where. There is also a big difference in the…

Post: Beware the Innodb Table Monitor

create a table (in any database you choose), like this: CREATE TABLE innodb_tabletable, every column, and every index in that table.  Once you get the output, you just DROP the table

Post: Recovering Innodb table Corruption

…: Page may be an index page where index id is 0 25556 InnoDB: (index “PRIMARY” of table “test”.”test”) InnoDB…force_recovery=1 you can do the following: mysql> CREATE TABLE `test2` ( -> `c` char(255) DEFAULT NULL, -> `id` int… 50 rows per page so not a big surprise 30 rows was not enough – typically …