… the same mysql prod instance . The first SQL query executed in 0.03 secs whereas the second SQL query when using index merge executes in 3.8 secs. I dont understand why MySQL uses index merge after a couple of mins when previously it was using a single index. I would greatly appreciate…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for the tests. Following is the configuration that I used: ## InnoDB… which has no secondary index and only a primary index, and against the table purchases_index which has 3 secondary indexes. Another thing I… we all now there are pros and cons to using secondary indexes. While secondary indexes cause read performance to improve, but they have…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… the cost of index maintenance. This experiment isn’t measuring the cost of index maintenance. Transaction… incorrect. The correct URL for version you used is http://tokutek.com/downloads/iiBench-1… Mark wrote http://bazaar.launchpad.net/~mdcallag/mysql-patch/mytools/annotate/head%3A/bench/ibench/…
Comment: InnoDB's gap locks
May 7, 2012 By 15 Comments
… inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread… space id 57 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 0 27638 lock…
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… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index… tables too: mysql> select sum(data_length+index_length) from information_schema.global_temporary_tables where engine=’memory’; +——————————-+ | sum(data_length+index_length…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… space id 19 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 72C lock_mode… of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and the gaps…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… note that the following changes were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer… that traditional index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index record…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… me briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What… that we have traded many point index lookups to one or more index range lookups. This means MySQL can employ many other optimizations… note that the following changes were made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… MySQL uses to determine whether to use index or filesort? On mysql manual I notice that the above query satisfies all conditions required for MySQL to use an index but…
