… important configuration that I am using is that I am using “estimate” flushing method available … float NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`customerid`)…
Comment: Multi Column indexes vs Index Merge
May 10, 2012 By 30 Comments
… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra… | SIMPLE | ProjectTbl2 | const | PRIMARY,namespace_ix | PRIMARY | 4 | const | 1 | Using filesort | | 1 | SIMPLE | LinksTbl2 | index_merge | tstamp,userid,converted_from… | prjid_ix,item_desc | 4,1 | NULL | 294 | Using intersect(prjid_ix,item_desc); Using where | | 1 | SIMPLE | PersonTbl2 | eq_ref | PRIMARY…
Comment: Zero-Downtime Schema Changes In MySQL
May 3, 2012 By 2 Comments
… column unique key, or indeed any unique key has been lifted in 2.1. Are there any known gotchas when using pt-osc… hope of using at wikipedia but was limited by having many tables with composite primary keys (or no pk / unique key at all…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… speedup that occurs when you get rid of the secondary keys is simply because you are writing 4 times fewer B-tree values. The primary key and each secondary key each incur nearly the same cost, and the… iibench is slightly incorrect. The correct URL for version you used is http://tokutek.com/downloads/iiBench-1.0.3.1…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… key values, and then instead of point primary key lookups, a range lookup is performed on the primary key by using the sorted primary key… type possible_keys key key_len ref rows filtered Extra 1 SIMPLE nation ALL PRIMARY NULL NULL NULL 25 100.00 Using temporary; Using filesort….o_orderkey 1 100.00 Using where b. Good Plan id select_type table type possible_keys key key_len ref rows filtered Extra…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…. Now let me briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more… is used in conjunction with BKA and that is MRR Key-ordered Scan. Let’s see what this optimization actually is. Key-ordered… much of a performance improvement from using Hash Join in MariaDB 5.5 or from Key-ordered Scan for TPCH query #3…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… condition is applied cannot be used for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
…, 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: 103, Extra: ”): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1698, 1 This one uses filesort (Extra: Using filesort…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME… SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS… read only for the duration of operation. You can also use Percona Xtrabackup, LVM or other database backup approach which does…
Comment: Predicting how long data load would take
March 26, 2012 By 12 Comments
… the partition rule, then by the primary key. The “partition rule” is usually mod(partition_key, ), so we found a good ordering… … select from source_tab … order by mod(target_partition_key, ), target_primary_key; This will load your partitions one-by-one, and… a 1.5 billion row table in “only” 30 hours using this approach.
