… memory usage you might look at how it uses MySQL to identify potential causes. Is it working with large blobs ? Using user variables… information_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…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… that we have traded many point index lookups to one or more index range lookups. This means MySQL can employ many other optimizations… Join. This join algorithm only works with equi-joins. Now let me briefly explain how hash join algorithm works. Suppose you have two… note that the following changes were made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5… 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…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…the following changes were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=…20; In-memory workload Now let’s see how effective is MRR when the workload fits …of secondary key lookups as well. But this works only with joins and specifically with Block …
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 5 Comments
… I was just hired at MySQL. The assumptions about what are typical MySQL may have been true …can be optimized quickly no matter how bad the pruning heuristics works. With heuristics, things can go arbitrarily …based on some syntactic properties and knowledge of indexes. The problem is that it is very …
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… that is large enough to hold your working set — the index blocks that are frequently used. How large is that? This is yet… an amount of memory based on how important those tables are to you, how big the indexes are on disk, and so on… and compare the results to your IO system’s capabilities. MySQL doesn’t have good instrumentation for scientifically choosing a key…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… index lookups. So we spoke about how MySQL uses the index but not exactly what it gets from the index – typically (unless it is covering index) MySQL… 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
… MySQL can use indexing, getting wrong ideas on what query performance they should expect. There are 3 main ways how MySQL can use the indexes… 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… you have both restriction and sorting things like this would work A=5 ORDER BY B ; A=5 ORDER BY B…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
… have a backup or the backup restore process doesn’t work. How can I recover deleted rows? We’re going to follow… to find the different indexes and types. mysql (information_schema) > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN…’; +———-+———+ | INDEX_ID | NAME | +———-+———+ | 26 | PRIMARY | | 27 | emp_no | +———-+———+ B) InnoDB Table Monitor The index information can also be taken directly from MySQL using…
Post: MySQL Consulting - Being on your own
February 20, 2007 By 7 Comments
…, especially if working with small project implemented by beginner. It may be as simple as adding few indexes and tuning MySQL Server settings… time. How are we helping people ? Even though we’re saying we’re focusing on MySQL Performance the scope of our work proved… solving customer problems because we have experience not only advising how to do the things but also implementing things ourselves, which…
