… 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
…: 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… * from innodb order by id \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pktest type: index possible_keys: NULL key: PRIMARY key_len: 4…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… with different typical data access patterns: primary key single row lookup, primary key range lookup, same access types for primary key and full table scans. To… query with access by primary key. InnoDB is faster than MyISAM by 6-9%. Falcon shows very bad scalabilty. READ_KEY_POINT Query: SELECT…
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…
