…, the number of these point primary key lookups will be depend on the number of rows that match the condition ‘key_column=x… in MariaDB 5.5 and not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL 5.6…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… lookups. This means MySQL can employ many other optimizations like for example if columns other then the secondary key columns are required to… disk, in fact half the amount of data is read from disk 2.2G vs 5G. However, there is one number in MariaDB 5.5 that is quite large as compared to MySQL 5.6 and that is…
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… new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… convert this into a query log, and examine the log. In MySQL 5.1 and newer with the InnoDB plugin, it’s… in SHOW PROCESSLIST, and look in the Host column; there you’ll see a hostname followed by a port number. That is the port number of the TCP connection that’s to blame for causing the trouble. If you’re in…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable to…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… to enable logging atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise we might… 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…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’…” field is selected in the list of columns to show (click show/hide to change the columns) That the…for testing. This way you can verify the numbers in my next blog post. I will compare …
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… combined index but single index on another column. This is because MySQL is able to estimate number of rows it will find using… do not know of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should be… multiple column indexes. This is in case you’re using OR between the columns. In this case the combined index is useless and MySQL has…
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
… do some testing on a large data set against two MySQL column-store storage engines. I’d like to note that this… for MySQL column-based storage engines in the past. I reported any bugs that I found, and contributed my test cases to ICE. In… 29 queries on the large data set. I compared a number of different factors between the two databases, including: Ease of…
Post: When should you store serialized objects in the database?
January 21, 2010 By 42 Comments
… they changed from storing data in MySQL columns to serializing data and just storing it inside TEXT/BLOB columns. It seems that since then…, if an age must be a number, and if the age must be unsigned. MySQL doesn’t go as far as… it’s difficult to store pieces of data like numbers or IP addresses in their most efficient form, and that technically this…
