… can create. If you try setting the storage engine to InnoDB however, you will get an error message! The exact error…’s right folks, in MySQL 5.6 you can create a table with a few more columns in InnoDB! This led me…(1..shift @ARGV) { my $n=cname($_,63); $sql.=”`$n`”; $sql.=” ENUM(‘”.cname(0,64).”‘) COMMENT ‘”.cname($_,254).”‘,\n”; } chop $sql; chop…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
… on MySQL server. Let’s take a recent 5.1 bzr version (pre-5.1.61) and a single test, innodb_plugin.innodb…: innobase_start_or_create_for_mysql (srv0start.c:1514) ==9090== by 0x6B48855: innobase_init(void*) (ha_innodb.cc:2284) ==9090== by 0x712F17…== by 0x74A32E: show_status_array(THD*, char const*, st_mysql_show_var*, enum_var_type, system_status_var*, char const*, st_table…
Post: Common MySQL traps webinar questions followup
January 17, 2013 By 1 Comment
… for a slave. So you may want to: relax InnoDB durability (innodb_flush_log_at_trx_commit) disable binary logging if it… some subtleties of ENUMs due to their dual nature. Q: Are these recommendations true of all versions of MySQL? If not which… good command of SQL query writing and wanted to learn MySQL administration suggest any book or practices. Of course I’m…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… being able to find out and then test that the ENUM limit of 65,535 has never been true (but now… is that MySQL may cause unnecessary disk IO for information it already has cached (often at least twice – in InnoDB itself and in the table_definition_cache). Further reading: MySQL Forge Internals document on the FRM…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… to present a small benchmark which shows MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table) columns… #10000 making MySQL to discard first 10000 records. 1) Results for ENUM: select SQL_NO_CACHE city from cities_enum WHERE state=’Minnesota… be larger. It is also interesting to note performance of Innodb tables in this case: for VARCHAR it takes about 0…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
… NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> show table status like “%bool%” \G *************************** 1. row *************************** Name: bbool Engine: InnoDB Version: 10…so – for most applications using TINYINT BIT(1) or ENUM for flags benefit would unlikely be worth the trouble….
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… NULL, `message_published` timestamp NOT NULL default CURRENT_TIMESTAMP, `kind` enum(‘link’,'img’) NOT NULL, `url_title` varchar(255) NOT NULL… feature to address this is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so page… the post – if you are badly missing some features in MySQL, InnoDB, InnoDB-plugin, XtraDB, XtraBackup – you know whom ask for!
Post: Lost innodb tables, xfs and binary grep
November 9, 2010 By 12 Comments
… how data was lost: MySQL had a dedicated partition on XFS file system Server was running innodb_file_per_table There was… their copy of the data The important tables were all InnoDB Having a backup, customer has first attempted to restore from…: 1. There was this rather big table with integer and enum columns only, where we knew a rather unique PK, well…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… with both – InnoDB and MyISAM. Now that more and more folks hit the InnoDB auto-inc scalability issue with MySQL 5.0 and… NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec) — 2. mysql> FLUSH TABLES WITH READ LOCK; Query… 1 Unfortunately, adding auto_increment does not work that way. Enum values (add and remove). Enumerated values are added and removed…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
…’re smart enough and read mysql performance related articles then you’ll store all those fields as enums or tiny int with… index will be between 5GB and size of data (for innodb). May be I’m wrong here, but I do think…/using-union-to-implement-loose-index-scan-to-mysql/#comment-1695 2. http://dev.mysql.com/doc/refman/5.1/en/index…
