I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in… limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… in MariaDB 5.5 and not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many… index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index record to make a lookup in…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…with MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use…schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_…I would say these are rather rare in MySQL. Most suspected memory leaks end up …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…in my previous post) to lookup the rows in table2. So this means that we have traded many point index lookups to one or more index…in MySQL 5.6 here and BKA in MariaDB 5.5 here. However, MariaDB 5.5 has one additional optimization that is used in… workload Now let’s see how effective are the join …
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…to other sessions. How to troubleshoot gap locks? Is possible to detect those gap locks using… (non repeatable reads) so in order to prevent problems with the …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: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
April 7, 2012 By 12 Comments
… Key and returned to MySQL server which will then in turn apply the remaining parts of the WHERE clause l_shipmode in (‘AIR’, ‘AIR… = ‘DELIVER IN PERSON’.” then how do you know it ? i mean, where can i found the optimization only use the prefix of index, but not the whole index…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index and how it is going to…we would like to only lookup people within single zip I would advice to use index in (zip,age) instead of using this workaround….
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
…indexes help to speed up queries even if data is in memory. If MySQL is only reading index and not accessing rows you will see “using index” in…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…in INFORMATION_SCHEMA that can help us to find the different indexes and types. mysql (information_schema) > select i.INDEX…To accomplish this task we’ll use constraint_parser command. In order to use it the tool needs to…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
Ever wondered how fast stored routines are in MySQL? I just ran a quick micro-benchmark to compare the speed of a stored function… similar to the first table shown above, but the further two rows are not shown. The query can’t be optimized to use indexes, and the stored function is opaque to the optimizer. This is why I purposefully wrote the subquery badly in the…
