… by SHOW TABLE STATUS versus 265 MB index size with the optimization disabled. OPTIMIZE TABLE OPTIMIZE TABLE is mapped to ALTER TABLE … ENGINE=InnoDB for InnoDB tables and thus, is just a special case of the previous one: mysql> SET expand_fast_index…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… 1272 index `k` of table `sbtest`.`sbtest` trx id 12305 lock mode S Aha! Now we have 2 tables in use and 2 tables locked… explicitly Innodb “converts” table lock to “no lock” hence eliminating conflicts on MySQL level table locks for most queries. Summary: MySQL Table level locks and Innodb Table…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… directly from MySQL using the InnoDB Tablespace Monitor. This monitor will write all the information related with tables and indexes (with their IDs) to the error log. mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB; TABLE: name employees/salaries…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
…and others not – it may be because the tables and indexes…
Post: How much space does empty Innodb table take ?
December 16, 2008 By 7 Comments
… small Innodb tables – they will take much more space than MyISAM. So .ibd file we get in case of table having no indexes (besides… be shown in Data_Length and Index_Length fields. Doing more tests with Inserts I can see Innodb seems to always try to… the following: Small Innodb tables will take more space on disk than one may anticipate. Using innodb_file_per_table can cause significant space…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
… simple: create an Innodb table make a copy of the .frm file do an ALTER TABLE to add an index then copy the old…, Baron) was adverse to trusting INNODB_SYS_INDEXES from some bad experiences with it, and suggested the Innodb Table monitor instead, see my next post for how that turned out, but this basically will regurgitate the entire Innodb…
Post: InnoDB TABLE/INDEX stats
March 20, 2010 By 7 Comments
…INNODB_TABLE_STATS INNODB_INDEX_STATS These table show statistics about InnoDB tables ( taken from InnoDB data dictionary). INNODB_TABLE_STATS is | table_name | table name in InnoDB internal style (‘database/table…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… 090420 12:09:32 INNODB TABLE MONITOR OUTPUT =========================================== ————————————– TABLE: name SYS_FOREIGN, id 0 11, columns 7, indexes 3, appr…. table, every column, and every index in that table. Once you get the output, you just DROP the table and it …
Post: Using innodb_sys_tables and innodb_sys_indexes
June 15, 2012 By Leave a Comment
… and when create secondary key by sort. Looking at innodb_sys_tables and innodb_sys_indexes I discovered it is not the case: mysql> select * from innodb_sys_tables; +———-+——–+——————+——+——–+——-+ | TABLE_ID | SCHEMA | NAME…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
… as indexes on UUID column or something similar. It also produces a lot better fill factor. The problem is…. OPTIMIZE TABLE for Innodb tables… it is OK table is exposed without indexes for some time. Note though nothing stops you from using LOCK TABLES on Innodb table to ensure there is not ton of queries starting reading table with no indexes and bringing box down…
