… and let us load with many transactions instead of one huge transaction. The smaller file is 4.1GB and has 260M… a million rows each. (InnoDB internally does operations such as ALTER TABLE in 10k row chunks, by the way; I chose 1M… 1 million rows at a time. Here’s the CREATE TABLE: CREATE TABLE load_test ( col1 bigint(20) NOT NULL, col2 bigint…
Comment: Learning about MySQL Table Fragmentation
November 19, 2008 By 20 Comments
… make a huge difference. In other cases one may not know prevailing access or may not have time to run alter table order by regularly. Running ALTER TABLE … ORDER BY … is often much slower than OPTIMIZE TABLE.
Comment: Finding your MySQL High-Availability solution – The questions
October 19, 2009 By 36 Comments
… latest versions) adding new ndbd nodes and of course doing alter tables. For alter tables, only replication can reduce the downtime. I agree that… have worked with NDB having 24 ndbd nodes handling insanely huge loads. 2.8 What the point here, do you use…
Comment: High-Performance Click Analysis with MySQL
December 23, 2008 By 13 Comments
… store strings – ID’s are just fine because the lookup table is small and you do few lookups anyway. The Denormalization… you can often get data clustering with MyISAM too by ALTER TABLE … ORDER BY X. It is not same as with Innodb… use full table scan MERGE TABLES are very efficient. What is however often a bad choice to have huge single highly indexed table which…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
… cases then difference in Performace with MyISAM and Innodb is huge for read only workload it is caused by the fact… be much faster as well as ALTER TABLE (expecially together with ability to create index without table rebuilding) Buffer pool improvements. I would… real one to unique. Tablespaces. To call “innodb_file_per_table” option tablespaces is a joke. It is better than nothing…
Post: Sharding and Time Base Partitioning
March 14, 2008 By 12 Comments
… about Wikipedia page version history for example which accumulates to huge volume which very few users need to read, various change… version creation. It may make sense to separate data on table (or partition) level to get better “clustering” – because data is… time to backup and restore it is easier to do ALTER TABLE and replication would not fall behind as easily. You can…
Post: BoardReader - Forum Search Engine
November 29, 2006 By 13 Comments
… “manual partitioning” scheme with different forum sites mapped to different “table groups” with each server handling bunch of these. This would… groups if needed as traffic growths as well as makes ALTER TABLE much less painful. The other technique which I covered in… quite interesting data, for example searching Britney Divorce will show huge spike then news came out and quick calm down in…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… | | ALICE FANTASIA | | ALIEN CENTER | | ALLEY EVOLUTION | | ALONE TRIP | | ALTER VICTORY | | AMADEUS HOLY | –Plus– Another example…ENGINE INNODB STATUS (which can be huge), you can call pager to the rescue…log’ mysql> use sakila Reading table information for completion of table and column names You…
Comment: How fast can you sort data with MySQL ?
September 24, 2007 By 26 Comments
On this subject.. if you have a huge un-ordered myisam table and wish to get it into sort order before dumping… then re-import c) Select using table-scan and sort as per your example d) alter to innodb with primary key (which…
Comment: Fighting MySQL Replication Lag
September 23, 2008 By 7 Comments
… if there was a comment way to do it, like: ALTER TABLE foo /* NO_BIN_LOG */ ADD INDEX foo_a_b (a…, but in my eyes that has always seemed like a huge pain that is full of potential pitfalls. We run with…
