…rows in set (0.00 sec) As you can see FLUSH TABLES WITH READ LOCK is waiting for that very nasty “full join” select…have similar option. This option can be especially helpful when backing up data…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… have the following query executed on the InnoDB table: SELECT non…table, the number of these point primary key lookups will be depend on the number of rows that…76882 102672 76832 Innodb_data_read 1.84G 1…in query times between MySQL 5.5 and MySQL 5.6/MariaDB 5.5 This is another area of improvement in…
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.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in… new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…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 my previous post) to lookup the rows in table2. So this means that we have…Another thing that is important to know is that…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…that table. This makes reads consistent and therefore makes the replication between servers consistent. If you execute SELECT…row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE_NAME=’salaries’; +————+ | ROW_FORMAT | +————+ | Compact | +————+ ~/recovery-tool# ./page_parser -5 -f data…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
…in one of the following ways: Timestamps (usually more than one) to identify rows that have…row to process SBR changes from a MySQL master. I’ll talk more about that in another…table is `test`.`demo`. mysql> select * from flexviews.test_demo\G *************************** 1. row…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…in the data dictionary: mysql> select * from flexviews.mview_table where mview_id= flexviews.get_id(‘demo’,'dashboard_customer_sales’)\G *************************** 1. row *************************** mview_table…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…rows affected (0.91 sec) Records: 229376 Duplicates: 0 Warnings: 0 Now you got all your data in MyISAM table so all you have…mysql> select max(id) from test2; +———+ | max(id) | +———+ | 220 | +———+ 1 row in set (0.00 sec) mysql> insert ignore into test2 select…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
…data from that row and replace the stored contents on disk C.) Use the data from that row to lookup up a row in another table…in MySQL coupled with the still large # of reads takes its toll. Another place where I have seen this kill clients is in apps that…
