…MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table participating in…in the join condition and then a hash table lookup is performed, on the hash table we created earlier. This step is known as…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…0, MySQL could only use one index per table in a given query without any …as the type of query and the type of index merge algorithm in the “Extra” field. Here’s an example of a table…table structure, or creating messy sub-selects. In cases where the optimizer just can’t get things right, and you, as…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…you a simple example. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column… mrr_buffer_size=4M Created_tmp_disk_tables 1 1 1 1 1 Created_tmp_tables 1 1 1 1…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…
Post: Filtered MySQL Replication
November 7, 2007 By 8 Comments
…in binary log – even for backup recovery, for example dealing with temporary tables (unless they are used to populate normal tables…selecting…created as BLACKHOLE. However you can’t do the same with MyISAM tables, because MyISAM can’t be disabled and so if you create tables as…
Post: Adventures in archiving
February 11, 2013 By 16 Comments
…tables. CREATE TABLE `archive_table…mysql> select id from archive_table order by id desc limit 1; +———+ | id | +———+ | 1143883 | +———+ 1 row in set (2.04 sec) mysql> select…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
…. CREATE TABLE TreePaths ( …table ‘TreePaths’ for update in FROM clause.” We can’t DELETE and SELECT from the same table in a single query in MySQL. But we can use MySQL…D-D as well as …in the process of moving (same applies to any other intra-subtree paths if it’s larger than just this simple example…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…mysql> SELECT * FROM cast_info WHERE role_id = 1 and note like ‘%Jaime%’; On a table like this: CREATE TABLE…as I said before, index condition pushdown. Let’s have a look at the EXPLAIN output: mysql> EXPLAIN SELECT…of MySQL 5.6, and actually is a great improvement over MySQL 5.5. For example, in this…
Post: MySQL Partitioning - can save you or kill you
December 11, 2010 By 45 Comments
… about using MySQL Partitioning for Performance Optimization …in BTREE is not that significant issue. So lets see at example: CREATE TABLE `tbl` …as couple of partitions which were actively used could fit in…selects still performed in the amount system needs. What is a take away ? When you’re creating…
Post: Upgrading MySQL
January 5, 2010 By 24 Comments
…MySQL 5.1 with no problems in most cases. When storage type changes, such as DECIMAL field got whole another meaning in MySQL 5.0 MySQL…example…In case fixing code to run on different MySQL version is taking a lot of time you may wish to create…SELECT queries on both new and old MySQL…
