… NOT NULL, PRIMARY KEY (`transactionid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (transactionid) (PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB, PARTITION…` float NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`customerid`) ) ENGINE=InnoDB AUTO_INCREMENT=11073789 DEFAULT…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… MRR: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) For… following: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Comment: Predicting how long data load would take
March 26, 2012 By 12 Comments
…-partitioned InnoDB tables is to sort by the partition rule, then by the primary key. The “partition rule” is usually mod(partition_key, ), so… … select from source_tab … order by mod(target_partition_key, ), target_primary_key; This will load your partitions one-by-one, and within…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… NULL, PRIMARY KEY (id), KEY (a), KEY (b) ) ENGINE=InnoDB Table 1 has 512000 randomly generated rows. This query uses index (type: index, key: a, key_len…
Comment: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
March 11, 2012 By 7 Comments
… mod http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html I have the same problem. 1… what it do innodb_autoinc_lock_mode=0 2. restart mysql server now is working perfectly id – is primary key name – is unique…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
March 7, 2012 By 21 Comments
I am using AWS MySQL RDS, InnoDB engine. Our requirement is to insert data in same table …_my_id (myid is 1st column of composite (5 columns) primary key). Reading and Writing in same table. For 22,518,912…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
Innodb primary key is special in many senses and I was always wondering how well MySQL is integrated with Innodb to take advantage of…: CREATE TABLE `innodb` ( `id` int(10) unsigned NOT NULL, `a` int(11) default NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… PRIMARY KEYs with Innodb tables due to the fact all other key will refer to the rows by primary key. I also recommended to use sequential primary keys… update CURRENT_TIMESTAMP, PRIMARY KEY (`url`,`thumb_width`,`thumb_height`) ) ENGINE=InnoDB; Why did I use this solution compared to others: Innodb Tables – This…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
… circumstances Innodb is actually faster than MyISAM in 2 cases out of 3. I guess the reasons are the following: Innodb primary key joins are very fast as data is clustered together with index and generally highly optimized Innodb builds hash indexes…
