…query will roughly be evaluated in following steps, without MRR: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column…to the following: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column… numbers for the counter Handler_read_key. This is because of how…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…columns are required to be fetched, then MRR interface can instead access the PK by arranging the secondary key values by PK column…to the benchmarks, to see the difference in numbers. Benchmark results For the purpose of this benchmark, I have used TPC-H Query… Now let’s see how effective are the …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 16 Comments
… workload consisting of a small number of concurrent queries (or only one) to demonstrate how much improvement could be made to MySQL so that is… post into a partitioned InnoDB table. Partitioning example: /*!50500 PARTITION BY RANGE COLUMNS(FlightDate) (PARTITION p2 VALUES LESS THAN (’1988-01-01…
Post: How To Test Your Upgrades - pt-upgrade
December 29, 2011 By 1 Comment
…column count, query execution time including execution errors and warnings. So how do you exactly test your queries…query log: # You can also run the slow log through pt-query-digest like the commands below # to limit the number of queries per fingerprint to…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… to note is the last line in the output the # MISC part, it tells you how much of “load” is not covered by… the query execution plan used by MySQL. The end result might be that you end up limiting the number of results returned by the query, by using a LIMIT clause or by filtering based on the option_name column, or you…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…by index is how many rows you need to scan to resolve order by (this can be found in slow query log or by…by by last column, in fact index can be used for ORDER BY if sorting is done by leading column(s). Note however columns following column…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…BY, such as this one, this creates a ranking function automatically. In cases where this is not useful, simply ignore this column…to display the total sales amount for any particular customer. Notice how you can use this summary to calculate the number…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…Query applies a clever approach to parallelism which allows it to significantly improve the performance of queries by…by joining the newly constructed dimension table tables to the staging tables, omitting the dimension columns…by the number…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… index lookups. So we spoke about how MySQL uses the index but not …query completes in less than 10ms Now what if we pretend we only have single column indexes (by hinting optimizer to…to ? It is not combined index but single index on another column. This is because MySQL is able to estimate number…
