… API (see convert.php below) Refresh methods The incremental refresh method The incremental refresh method uses table changelogs which are created by… MV gets a special column `mview$pk` which is an auto_increment BIGINT surrogate key for the table. For complete refresh views… affected (41 min 52.04 sec) *If you use MIN/MAX/COUNT_DISTINCT, a secondary view will be built in the…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
Comment: Blob Storage in Innodb
February 10, 2010 By 13 Comments
…_row_length: 1589248 Data_length: 1589248 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2010-02…_row_length: 32768 Data_length: 32768 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2010-02…
Comment: Finding out largest tables on MySQL Server
October 6, 2008 By 27 Comments
…_row_length: 150 Data_length: 2237661184 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2008-10…_row_length: 147 Data_length: 2237661184 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2008-10…
Comment: MySQL Partitioning - can save you or kill you
July 30, 2012 By 45 Comments
…(255) NOT NULL AUTO_INCREMENT, `word` text NOT NULL, `url` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=1309205 DEFAULT CHARSET=latin1…’ SELECT `word`,`url` FROM `tags` WHERE id >= FLOOR(RAND()*( SELECT MAX(id) FROM tags))
Post: PROCEDURE ANALYSE
March 23, 2009 By 9 Comments
…drupal.node.type Min_value: blog Max_value: webform Min_length: 4 Max_length: 13 Empties_or_… Default | Extra | +———-+——————+——+—–+———+—————-+ | nid | int(10) unsigned | NO | PRI | NULL | auto_increment | | vid | int(10) unsigned | NO | PRI | 0 | | | type …
Comment: Real-Life Use Case for "Barracuda" InnoDB File Format
April 24, 2008 By 11 Comments
… info: CREATE TABLE `document_queries` ( `id` int(11) NOT NULL AUTO_INCREMENT, `word_document_id` int(11) DEFAULT NULL, `html` mediumtext, `created…_row_length: 4266 Data_length: 42783473664 Max_data_length: 0 Index_length: 43614208 Data_free: 0 Auto_increment: 3442149 Create_time: 2008-04…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… yesterday: CREATE TABLE `sample` ( `i` int(10) unsigned NOT NULL auto_increment, `j` varchar(255) default NULL, PRIMARY KEY (`i`), KEY `j…_row_length: 61 Data_length: 100253696 Max_data_length: 0 Index_length: 128974848 Data_free: 0 Auto_increment: 1638401 Create_time: 2006-07…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…_row_length: 241 Data_length: 1616904192 Max_data_length: 0 Index_length: 539279360 Data_free: 4194304 Auto_increment: NULL Create_time: 2011-05…=# innodb-rollback-on-timeout innodb-rollback-segments=16 innodb-stats-auto-update=0 innodb-stats-on-metadata=0 innodb-stats-sample…-wait-timeout=300 log-error=/var/log/mysqld-innodb.log max-allowed-packet=1M net-buffer-length=16K #we value throughput…
Comment: MySQL Indexing: Best Practices Webinar
August 8, 2012 By 3 Comments
… log files are. For now what I do is an auto-increment primary key and an indexed datetime column, and use two… primary key range: mnm, mxm = db.do1(“SELECT MIN(pkey), MAX(pkey) FROM tbl ” “WHERE dt_field BETWEEN %s AND %s”, (min_date, max_date)) The second uses two BETWEEN clauses to benefit from…
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
… structure: CREATE TABLE t_x(i int(11) not null auto_increment primary key) And during the benchmark, these 30 tables were…-db=test –oltp-table-name=sbtest_20m –max-time=900 –num-threads=8 –max-requests=0 run Note that the tables that… the buffer pool size is 6G. The value of innodb_max_dirty_pages_pct was set at 90, while the InnoDB…
