…cause much confusion. Even though the read-only transactions optimization in MySQL 5.6 virtually eliminates contention on the mutex protecting …–mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | starting | 0.000032 | | checking permissions | 0.000005 | … snip … | init | 0.000021 | | optimizing…, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’batched… these variables here. The query used is: select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as revenue, o_orderdate, o…
Post: How to Identify Bad Queries in MySQL
January 10, 2011 By 16 Comments
… is a big part of optimization. A scientific optimization process can be simplified to “can anything …the system by grouping queries into classes and summing the execution time together. Then it prints …query executed for, which has been available in MySQL for years. This is necessary for determining …
Post: What do we optimize with mk-query-digest ?
December 15, 2009 By 2 Comments
… we typically look at the Queries causing the most impact (sum of the query execution times) as well as queries having… highest Impact are important because looking at these queries and optimizing them typically helps to improve system capacity, which in the… them are often high priority to be optimized. It helps if batch jobs use different MySQL user compared to the web site…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies…optimization enabled: ON DUPLICATE KEY UPDATE `origin_airport_id`=VALUES(`origin_airport_id`), `count(*)`=`count(*)` + VALUES(`count(*)`), `sum(AirTime)`=`sum(AirTime)` + VALUES(`sum(AirTime)`), `sum…
Post: Optimizing repeated subexpressions in MySQL
January 23, 2009 By 11 Comments
How smart is the MySQL optimizer? If it sees an expression repeated many times, does it …(@sum, 7), pow(@sum, 8), pow(@sum, 10), pow(@sum, 11), pow(@sum, 12), pow(@sum, 13), pow(@sum, 14), pow(@sum, 15), pow(@sum, 16), pow(@sum, 17), pow(@sum, 18); This query runs 500 iterations in 12s. So it looks like the optimizer was computing the SUM…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… in both directions. Explain for query Q1.1: mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from lineorder join…=0; mysql> set global innodb_old_blocks_time=0; Query OK, 0 rows affected (0.00 sec) mysql> select straight_join sum(lo….000000 | 0.001000 | 0 | 0 | 0 | 0 | 3 | 3 | | 8 | optimizing | 0.000048 | 0.000048 | 0.000000 | 0.000000 | 0 | 0…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…optimization which is required to efficiently refresh MV which use those aggregate functions. Now I delete some line items from orders: mysql…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
…optimized to force a bad execution plan. You should never use IN() subqueries the way I do, at least not in MySQL…cl.CountryCode = co.Code where lower(cl.Language) = ‘English’); +——————–+ | sum(ci.Population) | +——————–+ | 237134840 | +——————–+ 1 row in set (0….
