I have been working with Peter in preparation for the talk comparing the optimizer … 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…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… this means that we have traded many point index lookups to one or more index range lookups. This means MySQL can employ… Join. This join algorithm only works with equi-joins. Now let me briefly explain how hash join algorithm works. Suppose you have two… revenue, o_orderdate, o_shippriority from customer, orders, lineitem FORCE INDEX (i_l_orderkey) where c_mktsegment = ‘AUTOMOBILE’ and c_custkey…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… counts how many times the buffer used by MRR had to be reinitialized, because the buffer was small and not all index… how status counter values are incremented when index lookup is performed. As I explained at the start of the post that traditional index… the performance of secondary key lookups as well. But this works only with joins and specifically with Block Access Join Algorithms…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… memory usage you might look at how it uses MySQL to identify potential causes. Is it working with large blobs ? Using user…_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in set (0.98 sec) This…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 5 Comments
… any query can be optimized quickly no matter how bad the pruning heuristics works. With heuristics, things can go arbitrarily bad, that… search depth based on some syntactic properties and knowledge of indexes. The problem is that it is very hard to predict…
Post: How to find wrong indexing with glance view
August 21, 2008 By 27 Comments
… understand how indexing works and so index all columns used in the queries…. separately. So you end up with table which has say 20 indexes… need to have multiple column indexes for optimal performance. But wait. Do not go ahead and index all combinations. This would likely…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… work we do is often not simple at all. It’s impossible to predict. A customer may call and ask “how long…. You cannot consider any one of these factors (schema, queries, indexing) in isolation, because they are tightly bound together, and tweaking…. This is where you need to really know how to write queries and how EXPLAIN works. At Percona, we have peer training constantly…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause thinking this is the optimal indexing… index in this case. In case you’re using OR between columns – single column indexes are required for index merge to work and combined indexes…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… describes how MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index to… you have both restriction and sorting things like this would work A=5 ORDER BY B ; A=5 ORDER BY B…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
If you ever wondered how big is that or another index in InnoDB … you had to calculate it yourself by multiplying …, but you may have inexact results). Let’s see how it works. I have one table with size 13Gb what was filled… this is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so page fill factor should…
