June 19, 2013

Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available

April 17, 2013 By 2 Comments

… case mixed CHAR and VARCHAR columns would be used in foreign key… fixed #1130888 (Seppo Jaakola). If MySQL replication threads were started before…(Seppo Jaakola). Conflicting prepared statements in multi-master use case could cause…Prabhu). In case CREATE TABLE AS SELECT statement was running in …

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

Post: Multi Column indexes vs Index Merge

September 19, 2009 By 31 Comments

… STATE columns. The better strategy is often to have combined multi-column index on (AGE,STATE). Lets see why it is the case. MySQL… depends on selectivity and correlation. If where clause from first column selects 5% of the rows and applying where clause on second column brings…) As a summary: Use multi column indexes is typically best idea if you use AND between such columns in where clause. Index merge…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: Multi-Column IN clause - Unexpected MySQL Issue

April 4, 2008 By 21 Comments

…. I should not be surprised though as multi-column in is not the most used MySQL feature out there. For given application case we could simply rewrite query using more standard single column IN clause: mysql> explain SELECT…

Filed Under: Insight for Developers Tagged With: ,

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

March 21, 2012 By 10 Comments

… post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB 5.5…: SELECT non_key_column FROM tbl WHERE key_column=x This query will roughly be evaluated in following steps, without MRR: SELECT key_column, pk_column…: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer…

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

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

… prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5…. the new one. Note that ALTER is a multi-step process, the old table is locked in …DROP COLUMN c; session7 > select * from test_2 order by id; session8 > select * from test_2 order by id; session9 > select * …

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

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

… amazing MySQL manual, especially the option and …show_collationsblogpercona.commanual Com_show_column_typesblogpercona.commanual Com_show_…Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…

Filed Under: Insight for DBAs, MySQL

Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

March 12, 2012 By 12 Comments

… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…

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

Post: 3 ways MySQL uses indexes

September 12, 2009 By 28 Comments

… I see is multi column indexes which are used but only to their short prefix which is not very selective. A lot of this mistakes come from missing one very important MySQL limitation – once MySQL runs into the… be able to use full index for sorting (note MySQL may not select to use index for sort if you sort full…

Filed Under: Insight for Developers Tagged With:

Post: Shard-Query EC2 images available

May 11, 2011 By 2 Comments

…column. There will be another blog post about the usage and performance of the splitter. It is multi-threaded(actually multi…’; mysql> use ontime1; Database changed mysql> show table…selected in the list of columns to show (click show/hide to change the columns) That the “public dns” field is selected…

Filed Under: MySQL

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

March 4, 2013 By 7 Comments

…_length. First, MyISAM, with MySQL 5.5, on the SEO data set: mysql: SELECT id, title, MATCH(title,… table with a single VARCHAR column named “value”. OK, sounds easy enough: mysql: SHOW CREATE TABLE innodb_myisam_…may or may not be the case with multi-byte character sets or with ideographic languages …

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