…PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`customerid`) ) ENGINE=InnoDB…
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… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason why MySQL does not use index in the… 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… note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if…
Comment: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
March 11, 2012 By 7 Comments
Dear Miguel mysql from 5.1.22 change autoincrement mod http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling… investigate 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 these… effectively key on (a,id) which means MySQL could skip filesort if ordering is done by primary key: mysql> explain select id from innodb where a…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in MySQL 3….PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, KEY…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…+———-+———+ | 26 | PRIMARY | | 27 | emp_no | +———-+———+ B) InnoDB Table Monitor The index information can also be taken directly from MySQL using the InnoDB …result, 0-26 is our primary key. After identifying the Primary Key don’t forget to remove the innodb_table_monitor. C) …
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… MyISAM tables in case of MySQL/System crash is painful and great to be avoided. Long primary key – why did not I use…
