…auto_increment BIGINT surrogate key for the table. For complete refresh views which are ordered with ORDER BY, such as this one, this creates…in set (0.61 sec) Refreshing the MVs The view which uses the complete refresh method takes a very long time to refresh: mysql…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… an overview of how to install and run…in PHP so it is portable. Setting up FlexCDC FlexCDC has some basic requirements: MySQL 5…mysql> create table > test.demo ( > c1 int auto_increment primary key, > c2 int > ) > engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql> insert into test.demo values…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
…AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not: mysql> alter table am auto_increment=1000; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 3 Comments
…in Percona server 5.5.17-22.1. AUTO INCREMENTAL VALUES In order to have the same auto incremental values on master and slaves the actual used auto incremental value…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 53 Comments
…to run two separate queries. For my tests I’ve created following simple table: CREATE TABLE `count_test` ( `a` int(10) NOT NULL auto_increment…in set (0.00 sec) Here is why our count was much faster – MySQL accessed our table data when calculated result set…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…in set (0.00 sec) mysql> INSERT INTO `huge_table` (text) VALUES (‘test’); ERROR 1062 (23000): Duplicate entry ‘0‘ for key 1 Unfortunately, adding auto_increment…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t(c) VALUES…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
…how many physical cores are connected to my bus, and because I chose to create six hash “buckets” in the table. This allows MySQL to set…in set (0.00 sec) How to factor numbers create table dataset ( id bigint auto_increment…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…how it looks in practice: I used the same sample table as yesterday: CREATE TABLE `sample` ( `i` int(10) unsigned NOT NULL auto_increment…to the pages (column j was populated by md5(rand()) values) : mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql…
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
…set of rows for simplicity. CREATE TABLE `t1` ( `t1_c1` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`t1_c1`) ) ENGINE=InnoDB; CREATE TABLE…5.0 exhibits the above mentioned behavior on 5.1 and 5.5, MySQL will stop deleting rows if it encounters constraint errors. To demonstrate on 5.5…
