…the case with PXC but it’s also the same with PRM). But why and what are the risks ? The goal of…of their respective owners. Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the…2491595e99bb, 86928728-d56d-11e1-0800-f7c4916d8330, } joined { 7e6d285b-d56d-11e1-0800-2491595e99bb, } left { } partitioned…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
…the join code itself is likely to be responsible for less portion of… row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: j…00 sec) So what about Innodb ? Innodb executed the same query…join. So how do I read these results ? CHAR keys are indeed slower for joins…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…the faster one? That’s what this post is about. Let’s start with the MySQL query optimizer. The optimizer tries to choose the best join…
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
The other day I had a case with an awful performance of a rather simple join. It was a join…what I did – forced it to use key, forced a different join… 1 select_type: SIMPLE table: tb1 type: ALL …are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… and MySQL 5.6, the optimizer chooses the wrong query execution plan. Let’s take a look at what are the good and bad query execution plans. a. Bad Plan id select_type table type possible_keys key key… the concept of MRR to improve the performance of secondary key lookups as well. But this works only with joins and specifically with Block Access Join…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… when both the Hash Join and the Key-ordered Scan are disabled. IO bound workload Now let’s see how effective are the join optimizations when the workload… see what specific types of queries would benefit from Hash Join as compared to Nested Loop Join, but for now it looks like Nested Loop Join…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…-linux-glibc2. | | MAX_ALLOWED_PACKET | 1048576 | 4194304 | | JOIN_BUFFER_SIZE | 131072 | 262144 | | TMPDIR | /mnt/data/…0.05 sec) Lets go over to see what are the most important changes one needs to consider … differently now. The query_cache_type is now off by default with default size of 1MB while …
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
…the first post covered the following topics: What is a materialized view(MV)? It explained that an MV can pre-compute joins…the type of change (insert is 1, delete -1), the transaction order and the source of the changes, respectively. Finally, note that the two insertions happened inside of the…
Post: Intro to OLAP
July 12, 2010 By 10 Comments
…the performance of MOLAP with the convenience of ROLAP, but there are still challenges with this approach. The biggest challenges are the amount of…the fact table that don’t join to dimensions are either considered degenerate dimensions or measures. In the example below the status of the…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
… not provide any information of this type, Percona Server however adds number of tables to Information Schema…index_id) bp JOIN innodb_sys_indexes ON id = index_id JOIN innodb_sys_tables ON …are in buffer pool for given table (cnt), how many of them are dirty (dirty), and what is the percentage of…
