… involved in the migration of a large read-only InnoDB database to MyISAM (eventually packed). The only issue was that for one…. Not small… I calculated that with something like insert into MyISAM_table… select * from Innodb_table… would take about 10 days. The bottleneck… was simply to do insert into MyISAM_table select * from MyISAM_table1 and so on for the 16 files. Since MyISAM are flat files…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…. Fast Index creation will allow Innodb to build indexes by sorting rather by row by row insertions as it currently does which… will LOAD DATA INFILE be optimized same way as for MyISAM tables by separate phase of building Indexes ? Will be UNIQUE… Increment handling Yet another known Innodb problem is table locks which are taken for statement duration for Inserts with auto-increment column…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… application (storing billions of rows in tens of thousands tables) Innodb was better choice mainly because of thouse other behaviors … well…. MyISAM uses table locks and has concurrent inserts which can go concurrently with selects. This is sometimes presented as great concurrency for inserts… select statements but it has to be one insert at the time. Happily inserts in MyISAM are rather fast so it rarely is…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…. Now let us populate antest_innodb table which is same but uses Innodb format: mysql> insert into antest_innodb select * from antest; Query OK… – after loading the data with INSERT in Innodb table we do not get NULL cardinality as with MyISAM but instead we get very… significantly. This means Innodb and MyISAM have different stats computation method by default. Lets check how stats change for MyISAM if we change…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
… difference in Performace with MyISAM and Innodb is huge for read only workload it is caused by the fact Innodb does not have… times space compression. Insert buffer for delete. For Insert operation Innodb uses nice tecnique called Insert Buffer which speeds up inserts for non-unique indexes…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… tables which is being read to be Innodb – even if writes are done in MyISAM table. So why was this done, being… Innodb would not locking rows in source table other transaction could modify the row and commit before transaction which is running INSERT…/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO tbl2 SELECT * from tbl1; INSERT … INTO OUTFILE does not have to set extra…
Comment: Innodb Performance Optimization Basics
April 25, 2008 By 75 Comments
Lance – MyISAM can be faster than Innodb to insert the data and Innodb can be faster than MyISAM to insert the data – it all depends on what you’re looking at. For inserts… Innodb, however if you have large system and many concurrent inserts or parallel long running queries Innodb is likely to be faster because MyISAM…
Comment: Should you move from MyISAM to Innodb ?
April 8, 2009 By 59 Comments
…InnoDB; INSERT INTO InnoDB_animals (grp,name) VALUES (‘mammal’,'dog’),(‘mammal’,'cat’),(‘bird’,'penguin’),(‘fish’,'lax’),(‘mammal’,'whale’),(‘bird’,'ostrich’); SELECT * FROM InnoDB…
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
… is something nasty going on on Innodb level, so I tried running the test with MyISAM tables instead. The process completed in… 40 seconds for single row statements. So with single statement MyISAM was about 10% faster, which is quite expected but with…” SQL doesn’t have “SELECT” so, InnoDB scan all of the SQL each times… (* “INSERT” may not use this function? “UPDATE” may…
Post: High Rate insertion with MySQL and Innodb
January 7, 2011 By 17 Comments
… needs high insertion rate for data which generally fits in memory. Last time I worked with similar system it used MyISAM and… and we could get over 200K of inserts/sec. This time I worked with Innodb tables… it was a different system with… your select queries dramatically. The inserts in this case of course are bulk inserts… using single value inserts you would get much lower…
