… 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 these point primary key lookups will be depend on the number of rows that match the condition ‘key_column=x’. You… 5.6 is also reporting increased numbers for the counter Handler_read_key. This is because of how status counter values are incremented…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… with equi-joins. Now let me briefly explain how hash join algorithm works. Suppose you have two… first obvious improvement is shown by the high numbers of Innodb_buffer_pool_read_ahead when the buffers ….6. Next interesting thing are the last two columns of the table above and the values for ‘Handler…
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 the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: How number of columns affects performance ?
September 28, 2009 By 10 Comments
… rows, however it is not very significant if number of columns is small. For large number of columns dynamic rows become very expensive and you have… BTREE index very similarly to how indexes are stored. Summary: Beware of dynamic row format tables with many columns they might bite you…
Post: Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
… with many columns (the same one Peter wrote about recently). The client’s performance is suffering in part because of the number of columns, which is over 200. The queries are generally pretty simple (sums of columns), but they’re ad-hoc (can access any columns… loading the data with SQL statements. I wanted to see how fast MyISAM vs. MonetDB would interpret really large INSERT statements…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
… couple of small rows, but require at least 16K page in Innodb), keeping all tables open in Innodb dictionary and number of other… number of tables dramatically – merging say 1000 of users to the same table as VIEWs are significantly less expensive and cheap to “open”. How… base tables instead of views. BEFORE INSERT triggers would not help because the column user_id is not part of the view so…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… line in the output the # MISC part, it tells you how much of “load” is not covered by top queries, we have… the Count of number of times this query was executed. Now let’s take a look at the values in the 95% column, we… the number of results returned by the query, by using a LIMIT clause or by filtering based on the option_name column, or…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… commonly see among MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in… in fact several separate index lookups. So we spoke about how MySQL uses the index but not exactly what it gets… index but single index on another column. This is because MySQL is able to estimate number of rows it will find using both…
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
… in number of other environments. INSERT ON DUPLICATE KEY UPDATE had a unfair share of replication issues in MySQL 5.0. There are number of ways the problem can be solved but first we decide to see how… is different in the tables. It turned out one of the float columns stored “-0″ in MySQL 5.0 but it was…
Post: How many partitions can you have ?
December 5, 2009 By 18 Comments
… ranged number of partitions from 1 to 1000 and loaded the table with 1000000 of sequential values from 1 to 1million (the C column… partitions, which means it slows down 2.5 times as number of partitions increases to 1000. This regression is somethat surprising as… the meanwhile the data point is simple – be careful with number of partitions you use. Creating unused partitions for future use may…
