…aggregation queries over time. I realised that I could apply these same mathematical concepts to distributed queries… SQL, but right now only MySQL storage nodes are supported. Amdahl’…by 1; –PARALLEL OPTIMIZATIONS: * Base level table name: `aggregation_tmp#74082863` * IN list optimization enabled * …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…view, making it available for querying: mysql> call flexviews.enable( -> flexviews.get_id(‘demo’,'dashboard_customer_sales’)); Query OK, 0 rows affected (41…an optimization which is required to efficiently refresh MV which use those aggregate functions. Now I delete some line items from orders: mysql…
Post: How to Identify Bad Queries in MySQL
January 10, 2011 By 16 Comments
…Queries are bad either singly (an individual query is bad) or in aggregate (a group or class of queries…whether the query is likely to be possible to optimize. It finds queries or groups of queries …the query executed, not merely the duration the query executed for, which has been available in MySQL…
Post: What do we optimize with mk-query-digest ?
December 15, 2009 By 2 Comments
… queries having some longest samples. Why are we looking at these ? Queries with highest Impact are important because looking at these queries and optimizing… queries, which have real users waiting for them are often high priority to be optimized. It helps if batch jobs use different MySQL… have to look at it, rather than aggregating data for the all server – understanding what queries are being ran (together with other…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
…mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | starting | 0.000032 | | checking permissions | 0.000005 | … snip … | init | 0.000021 | | optimizing…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
…aggregated per all queries which is great, but I still need to figure out what queries…optimize the queries all together! Unfortunately mk-query-digest won’t do that for you, but then there’s mk-query…
Post: Using flow control functions for performance monitoring queries
May 24, 2008 By 12 Comments
… MySQL Queries as they are often abused used to create queries which are poorly readable as well as can hardly be optimized well by MySQL Optimizer. One way I find IF statement very useful is computing multiple aggregates over different set of rows in the single query…
Post: Performance impact of complex queries
January 17, 2007 By 11 Comments
… of MySQL Performance by complex queries on large data sets(ie some large aggregate queries) and batch jobs. It is not rare to see queries… – query which crunches large amount of data set wipes data from your normal working set from OS cache. Operation Systems and MySQL…. Number of them would try to optimize throughput before latency, meaning if you have query which is doing full table scan (sequential…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… this with vanilla MySQL, you will need to aggregate your data. What you want to do is aggregate in ways that optimize the most… queries over 365-day ranges for year-over-year analysis, aggregate again by month. Then write your queries to use the most aggregated data possible to save work. Avoid operations that update huge chunks of aggregated data at…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 16 Comments
… that a single query executes in a single thread. MySQL never takes advantage of more than a single CPU when aggregating data and… of concurrent queries which each examine large amounts of data. Since a single query is single threaded in MySQL, the new optimizations don’t… of queries tests the most basic aggregation (count(*)) on a range of records. This table is partitioned by month which means that MySQL…
