June 19, 2013

Post: Understanding the maximum number of columns in a MySQL table

April 8, 2013 By 5 Comments

…of columns is this: /* Hack to avoid bugs with small static rows in MySQL */ reclength=max(file->min_record_length(table…columns, you can only have 255 ENUM columns with unique values…same as what I can create. That’s right folks, in MySQL 5.6 you can create a table with a few more columns…

Filed Under: MySQL Tagged With: , , , , , , , ,

Post: Is Synchronous Replication right for your app?

May 14, 2013 By 18 Comments

…) between any two nodes in … touch the same row.  Row locks are…column to the users_groups table so we don’t need to update the parent group row so often? INSERT INTO users_groups (user_id, group_id, joined) VALUES…of us are used to the tradeoffs we take when deploying conventional stand-alone MySQL…

Filed Under: MySQL, Percona Software, XtraDB Cluster Tagged With: , , , , , ,

Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL

May 1, 2013 By 10 Comments

…MySQL is a hot…same enabled. MyISAM in PXC May Lead to Inconsistencies and May Not Even Work! MyISAM tables…-Incrementing Columns PXC/Galera controls auto-incrementing values … described on these two bugs. Galera use… master can use ROW based binlog format … when automatic creation of primary keys for …

Filed Under: Insight for DBAs, MySQL, Percona Software, XtraDB Cluster Tagged With: , , , ,

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

…value of ‘pk_column‘ which is a part of the secondary key tuple, a point primary key lookup is made against base table, the number of…_read_key and Innodb_rows_read to be incremented by TWO. It does not …value for counter Handler_read_key is more or less the same for MariaDB 5.5 when compared to MySQL…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…column `mview$pk` which is an auto_increment BIGINT surrogate key for the table…table, three orders of magnitude more quickly than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…of two things will happen; either the results returned from a MyISAM FTS query will be exactly identical to the same…value of ‘test/innodb_myisam_stopword’ And here’s what appeared in the server’s error log: InnoDB: invalid column type for stopword table…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… you need. Because I’ve built two such systems to manage online ads …tables… Optimize For I/O It is pretty much inevitable: if you do this kind of data processing in MySQL…column, and we just moved those other values around to store them “next, to in the same row” instead of “below, in the next row…

Filed Under: Insight for Developers Tagged With: , , , ,

Post: On Character Sets and Disappearing Tables

June 21, 2012 By 1 Comment

…VALUES (‘value_fk1_to_2′, ‘value_fk1_to_3′, ‘value_fk3_to_1′); INSERT INTO dos2 VALUES (‘value_fk1_to_2′, ‘value…of InnoDB file formats.  Moreover, trying to modify any of the remaining tables could cause the same…of an inconsistency present when MySQL won’t allow us to change an FK-referenced column…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…value of an ID column that you defined for the underlying table, or it can be a sequence value generated by InnoDB when the table…MySQL 5.6, and that was to change innodb_ft_min_token_size from the default of 3 to a value of…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

…of two phases, a build phase and a probe phase. Let’s consider the case of joining two tables…row scanned performing a lookup in the hash table by using values of the columns participating in the equijoin condition. The hash table…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , ,
« Older Posts