…InnoDB table…converted a lot of point primary key lookups to one or more range primary key lookup. Thereby, converting Random access to…Innodb_rows_read are incremented by ONE. However, when MRR is used then there are two separate calls made to the storage engine to…
Post: Quick tip: how to convert tables to InnoDB
July 30, 2008 By 4 Comments
… a bazillion tables to convert from MyISAM to InnoDB, but they are mixed in with other tables that are already InnoDB, or are another storage engine that you don’t want to touch. mk-find –engine MyISAM –exec “ALTER TABLE %D.%N ENGINE=INNODB…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 7 Comments
… to Innodb ” mysql -u root -e “alter table recover.$CURRENT_TABLENAME engine=Innodb;” sleep 1 echo “discarding tablespace ” mysql -u root -e “alter table…schema, the recovery completed. The customer was able to download the tables in MyISAM format and then he just convert them back to InnoDB….
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…tables and indexes (with their IDs) to the error log. mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB; TABLE…table_defs.h so it will be necessary to recompile the tool. To help us in the task of converting the schema definition to…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…table so all you have to do is to drop old table and convert new table back to Innodb after restarting without innodb…how to get your data back from simple Innodb Table Corruption. In more complex cases you may need to use higher innodb_force_recovery modes to…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… NULL, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select …to do is to convert Innodb table to latin1 character set. I was expected this to shorten some internal buffers MySQL has to…to 6.1 seconds which makes it over 2 times slower compared to integer based join. So how…
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
…InnoDB storage engine: CREATE TABLE `t1` ( `char_id` char(6) NOT NULL, `v` varchar(128) NOT NULL, PRIMARY KEY (`char_id`) ) ENGINE=InnoDB…seconds to execute with t1 as utf8 and t2 as latin1. Converting both tables to …Notice here how there is no CONVERT() required and the key_len on both tables match…
Post: Innodb row size limitation
April 7, 2011 By 11 Comments
…Suppose you have the following table structure: CREATE TABLE example ( id INT UNSIGNED … TEXT, fterms TEXT, PRIMARY KEY (id) ) Engine=InnoDB; Now you insert some test data … error after converting from a native language character set to utf8, as …t have to change code in a lot of places. How much you…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
…to make InnoDB use a composite UNIQUE key on a table…how to create indexes. Foreign key references may add processing time to…converting your varchars to chars for example), then having to…
Post: What exactly is read_rnd_buffer_size
July 24, 2007 By 4 Comments
…to – SQL or storage engine. Honestly as it had name very similar to read_buffer_size which is currently only used by MyISAM tables…converted to fixed size (basically everything but BLOB/TEXT) MySQL can use read_rnd_buffer to… sometime to see how it really impacts performance both for MyISAM and Innodb…
