… know InnoDB PK (Primary Key) contains all data, and all secondary indexes are only subset of columns of Primary Key. So in theory we can store… innobackupex –compact and it will create a backup where all InnoDB tables contain only Primary Keys and not secondary. It allows to safe some…
Post: Logging Deadlock errors
September 19, 2012 By 4 Comments
…query` text NOT NULL, PRIMARY KEY (`server`,`ts`,`thread`) ) ENGINE=InnoDB DEFAULT CHARSET=…update City SET name=’New york2′ where name=’New York’ *************************** 2. row …store logs on a centralized database. In MySQL 5.6 will be possible to log all deadlocks on error log using innodb…
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
…Innodb and vice versa. You may benefit from different index structure for Innodb, including different primary key…storing data in MyISAM. The 3 most important values to check are innodb_flush_log_at_trx_commit, innodb_buffer_pool_size and innodb…
Post: Stored Function to generate Sequences
April 2, 2008 By 17 Comments
… sequence generation process to the stored procedure and even though I …=last_insert_id(val+1) where name=seq_name; return last_insert_id(); …, `val` int(10) unsigned NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=…MySQL 5.0 you may with to use them instead of auto_increment with Innodb…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
… The binary log stores it progress in a metadata table: $ mysql -e ‘select * …mysql> create table > test.demo ( > c1 int auto_increment primary key, > c2 int > ) > engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql… in a second transaction: mysql> delete from test.demo where c1=1; Query OK,…
Post: Recovering from a bad UPDATE statement
October 19, 2012 By 5 Comments
… users table without a WHERE clause? Did you know that in MySQL 5.5 that sometimes…AUTO_INCREMENT, `c2` varchar(10) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB; INSERT INTO `t1` (`c2`) VALUES (‘michael’), (‘peter’),…new versions of the changed column is stored in the binary log — in this example in…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
…Primary Key or any other key. This is good for some applications bad for others. There is large amount of applications out where…Innodb (by using its internal hidden primary key) but this functionality is not exported well as it requires you to get rid of primary keys…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
…KEY_POINT with only different the values of accessed column is stored in key. Interesting InnoDB…WHERE id between %d and %d Access by range of PK values. PBXT is much worse here because of InnoDB clustering by primary key. InnoDB…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… can be much more modest. DVD Store was special > type of application… make InnoDB use a composite UNIQUE key on a table as a primary key … background. > Things are different with MySQL. Good design will help dictate… > SELECT * FROM articles WHERE user_id IN (SELECT id FROM users WHERE featured=1) …
Post: Recover BLOB fields
July 1, 2010 By 4 Comments
… a B-tree. Primary index is used as a key. Inside a page records are stored in a linked list. InnoDB page by default…_FIELDS` int(10), PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Here COMPACT format is used, which is default in MySQL >= 5.1. The… number of bytes to store all flags. So called extra bytes. These are 5 bytes where different flags are stored like “record is…
