…mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT…
Post: InnoDB Full-text Search in MySQL 5.6 (part 1)
February 26, 2013 By 1 Comment
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
…NULL AUTO_INCREMENT, `c` char(64) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB AUTO_INCREMENT=12582913 DEFAULT CHARSET=latin1 mysql>…test.a | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.a | optimize | status | OK | …
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… Dell DVD Store optimization, and I’m now on my own, offering MySQL and LAMP Consuilting Services. 2. Not using auto_increment functionality This is right. With some exception however. For example Innodb tables do internal full table lock if auto_increment is…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… either created, or last refeshed. The incremental method is somewhat conceptually similar to using mysql binary logs for point-in-time recovery… MV gets a special column `mview$pk` which is an auto_increment BIGINT surrogate key for the table. For complete refresh views… optimization which is required to efficiently refresh MV which use those aggregate functions. Now I delete some line items from orders: mysql…
Post: Announcing Percona Server 5.1.66-14.2
January 15, 2013 By Leave a Comment
… available here and from the Percona Software Repositories). Based on MySQL 5.1.66, including all the bug fixes in it… if the optimization is applicable with respect to AUTO_INCREMENT columns. Bug fixed #1039536 (Alexey Kopytov). In cases where indexes with AUTO_INCREMENT columns where…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… with vanilla MySQL, you will need to aggregate your data. What you want to do is aggregate in ways that optimize the…/XtraDB tables… Optimize For I/O It is pretty much inevitable: if you do this kind of data processing in MySQL, you… primary key as (day, ad). Don’t use an auto-increment primary key, and don’t put ad first. If…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’…: 0 Index_length: 539279360 Data_free: 4194304 Auto_increment: NULL Create_time: 2011-05-10 04:…throughput over response time, get a good plan optimizer-prune-level=0 partition=ON port=3306 …
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
…` ( `id` int(10) unsigned NOT NULL auto_increment, `pad` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) mysql> select count(*) from dim; +———-+ | count(*) | +———-+ | 30720 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key constrains. #859078…KEY. #851674 (Alexey Kopytov). Backported fix for MySQL bug #53761 (Wrong estimate for RANGE … failure in InnoDB. #814404 (Yasufumi Kinoshita). Since AUTO_INCREMENT columns must be defined as keys, …
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
…from the Percona Software Repositories). Based on MySQL 5.5.16, including all the …creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key constrains. #859078 (… failure in InnoDB. #814404 (Yasufumi Kinoshita). Since AUTO_INCREMENT columns must be defined as keys, omitting…
