…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: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… which are found and returned up to the top level MySQL part for processing are counted the Rows_examined remains zero… to the queries without aggregate functions/group by and only to ones which examine one table only. What is about queries which query more… way to see if query is worth optimizing ? – see how many rows query sends after group by, distinct and aggregate functions are removed (A…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 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: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… optimization in many cases. When you apply it though remember hash can have collisions so you will need to have your queries… key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary key… in index order MySQL gets data in already sorted order and looks at “one group at the time”, computing aggregate functions as…
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 12 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…
