… Meta Data Locking behavior prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5.5.3 a… row in set (0.00 sec) session2 > ALTER TABLE test add column c char(32) default ‘dummy_text’; Query OK, 2 rows affected…, 0 rows affected (0.00 sec) session2 > ALTER TABLE test add column c char(32) default ‘dummy_text’; Query OK, 2 rows affected…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… not look too bad. MySQL is using three different indexes to search approximately 8100 out of 4.5M rows to return the data we want. Since we’re only requesting the user_id column… search space closer to 8.5M rows, not 8100, because it has to read the index entries for each of the columns in the…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 5 Comments
… included in MySQL in 2008): The key columns must come before any non-key variable-length columns. For performance reasons, the key columns are stored in a fixed-length block at the beginning of the row. If there were any variable-length column in between them, it had to be stored in the…
Post: Debugging problems with row based replication
May 6, 2010 By 12 Comments
… the conditions under which replication went out of sync? To determine exactly what row is having an issue, we can use the… include column names. Column names are not included in the binary log, only positions. mysqlbinlog mysql_sandbox25162-relay-bin.000005 –base64-output=decode-rows…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
… Log is the MySQL log which records changes to tables in the database. FlexCDC reads the binary log to determine what rows have changed… possible to set up a dedicated MySQL slave which has log_slave_updates=1 and binlog_format=row to process SBR changes from a MySQL… change logs contain three additional metadata columns: dml_type, uow_id and fv$server_id. These columns represent the type of change…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… is important to look at key_len column in explain plan to see how many index parts are actually used for row lookup. Very common problem I see is multi column… 10 range. It is interesting to note this limitation only applies to interval ranges – for enumerated ranges MySQL will use both key parts…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… there are some MySQL optimizer restrictions in how well it can deal with primary key appended to the index column, especially in such… go to Table1 to lookup row by primary key when you do not need an index on a field which is used to join… column MySQL can avoid temporary table or filesort for group by by this column. This works because by scanning data in index order MySQL…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This turned into…_INCREMENT=22187769 DEFAULT CHARSET=utf8; The table had 22 million rows, with approximately 8 million of them having role_id = 1… data (just one column) and can take advantage of the covering index technique, did actually take more time to execute: mysql> SELECT role…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… the primary key. Therefore if we want to extract the rows data we need to identify which of those two indexes is the… log. mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB; TABLE: name employees/salaries, id 18, flags 1, columns 7…_date, salary, to_date); Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql (employees) > select…
Post: Webinar: Building a highly scaleable distributed row, document or column store with MySQL and Shard-Query
February 6, 2013 By Leave a Comment
… a webinar entitled “Building a highly scaleable distributed row, document or column store with MySQL and Shard-Query” The first part of this… the techniques employed by Shard-Query to implement a distributed MySQL database. The focus will then proceed to the types of distributed (massively…
