…NOT NULL, `productid` int(11) NOT NULL, `price` float NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY…to using secondary indexes. While secondary indexes cause read performance to…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
…. The primary key and each secondary key each incur…when the iiBench project started, today the challenge would be to index 10B rows, not…URL for version you used is http://tokutek…mysql-patch/mytools/annotate/head%3A/bench/ibench/iibench.py) is that it measures query performance too, so if you fail to…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL…index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in set (0.98 sec) This however will not…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… do not belong to the secondary index definition (and hence covering index optimization is not used), then primary key lookups have to be performed for each secondary key entry… for the counter Handler_read_key. This is because of how status counter values are incremented when index lookup is performed. As I… switch to using plan ‘a’, which does not involve MRR, and the query time for MySQL 5.6 and MariaDB 5.5 jumps to ~11min…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… Key Access (BKA) optimization was introduced. When BKA is being used then, after the selected rows are read from table1, the values of indexed… performance gain. This is not entirely visible in the manual either for MariaDB or MySQL, but you need to appropriately increase read_rnd…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…between index records. Thanks to this gap lock, when …session, not only the affected index record … for foreign-key constraint checking or duplicate-key checking….MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and the gaps to avoid phantom reads and to…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
…mysql-slow-queries.log long_query_time=15 log-queries-not-using-indexes query_cache_type=1 query_cache_size=32M thread_cache_size=30 table_cache=4096 join_buffer_size=6M key…mysql/drbd expire_logs_days = 5 max_binlog_size = 100M [client] socket=/data/mysql/mysql.sock When… to…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… is understanding when exactly MySQL is going to use index and how it is going to do them. So if you have table people with KEY(age… point. So MySQL Will not use indexes in all cases when it is technically possible. For multiple key part indexes MySQL will only be able to use multiple keyparts if…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
…not to use auto_increment but use certain partitioned sequences, for example you can have 256 growing sequences (with high byte used…when it comes to auto_increment columns, which is however completely separate problem which can be fixed in MySQL…
