…querying: mysql> call flexviews.enable( -> flexviews.get_id(‘demo’,'dashboard_customer_sales’)); Query…mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] {msandbox} (test) > select… has been increased from 500 to 5000. If you’re using innodb_thread_concurrency …on,engine_condition_pushdown=on 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * …
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…query is only selecting on vendor, will the index have any impact on the speed of the query? A: If…complicated conditions you will need to use something like Trick “Unionizing Order by” described in presentation…works because by scanning data in index order MySQL gets data in already sorted order and …
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…in the data set. mysql> select count(*) from dim_airport; +———-+ | count(*) | +———-+ | 396 | +———-+ 1 row in…queries feature at least one join, and many of the filter conditions are placed on attributes in…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5… is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… means many point queries, say for example if table1 yields 1000 …selective queries, and obviously when the first operand used in the build step is such that the hash table fits in…in Software RAID5. Also note that the following changes were made on MySQL 5.6 config: optimizer_switch=’index_condition…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…SELECT non_key_column FROM tbl WHERE key_column=x This query will roughly be evaluated in following steps, without MRR: SELECT…in Software RAID5. Also note that the following changes were made in the MySQL config: optimizer_switch=’index_condition…index tuples could fit in the buffer. If this is > 0 …
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
…queries are problematic in MySQL, it’s best to show some execution plans on dummy data: EXPLAIN SELECT …conditions (we are just no longer using an index on them): EXPLAIN SELECT * FROM coordinates WHERE x_floor in…) that fitted in memory, but the original query would have full table scanned if I didn’…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… ORDER BY with LIMIT in the back end. In general this type of ORDER BY looks like: SELECT ….. WHERE [conditions] ORDER BY [sort] LIMIT… may end up in slow query log as query which does not use indexes, even if it is quite fast: mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id | select…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…in talking to the customer, I discovered that it’s this particular set of conditions which was *always* used by this query… the same time ends up taking longer – if we stopped after retrieving the 17k rows… MySQL should be looking at, we can rewrite the query like so, with the following result: SELECT…
