…MySQL can employ many other optimizations like for example if columns other then the secondary key columns…changed to become sequential. The two other most important numbers are values for Innodb_buffer_pool_reads and Innodb_data… run tests to see what specific types of queries would benefit from Hash…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…changed to the following: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column… mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read…53G 1.89G 1.53G Innodb_data_reads 120552 123872 100551 103011 … a. Bad Plan id select_type table type possible_keys key key_len ref…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… this type of software was not available for MySQL. Change Data Capture can be used to: Monitor a database table, or tables for changes. Improve… five. All change logs contain three additional metadata columns: dml_type, uow_id and fv$server_id. These columns represent the type of change (insert is…
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
…to do some testing on a large data set against two MySQL column-store storage engines. I’d …I contributed. InfiniDB does not support as many data types and aggregate functions as ICE, and therefore it…returned unexpected results as well. I did not change any “out of the box” settings for Infobright…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
…. MySQL indexes are (with few exceptions) BTREE indexes – this index type is very good to be able to quickly lookup the data on…. I’ve changed the table to make columns i1 and i2 highly correlated. In fact they are now the same: mysql [localhost] {msandbox…. now if we change it: If we disable index on the second column we get a full table scan: mysql [localhost] {msandbox} (test…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…incorrect (out of sync) when the underlying data changes. This loss of synchronization is sometimes called …ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql> select…table’). The second parameter reflects the refresh type. This parameter can take the options: ‘BOTH’,'…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… want to build your aggregate tables to avoid joins. Watch Data Types Does your ad ID look like “8a4dabde-1c82-102c-ab13…. Use the smallest data types you can, the simplest character sets you can, and watch out for NULLable columns. Use smallint…’ll get there, it can change the lifecycle of your application in advance. What about partitioning in MySQL 5.1? I…
Post: Converting Character Sets
March 17, 2009 By 16 Comments
…changing each data type to it’s binary counterpart before conversion. Due to implementation limitations, however, it also converts any pre-existing binary columns…target column Comma-separated. NO SPACES. table.column –ignore-tables A comma-separated list of tables to ignore –password|p The MySQL …
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… not change “parent_id” column you can update data just fine, however if you want to change the parent_id column, even if you change it… satisfy foreign key conditions (have column child table is referencing and have it of proper type and indexed): mysql> set foreign_key_checks…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL …_show_column_typesblogpercona….change_bufferingblogpercona.commanual innodb_checksumsblogpercona.commanual innodb_commit_concurrencyblogpercona.commanual innodb_concurrency_ticketsblogpercona.commanual innodb_data_file_pathblogpercona.commanual Innodb_data… slave_type_conversionsblogpercona…
