….10 | | BACK_LOG | 50 | 80 | | OPEN_FILES_LIMIT | 1024 | 5000 | | INNODB_AUTOEXTEND_INCREMENT | 8 | 64 | |… has been increased from 500 to 5000. If you’re using innodb_thread_concurrency this…_intersection=on,engine_condition_pushdown=on 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) >…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…from data clustering in the different way or if you have some other natural candidate for …in recent MySQL versions. However it is often too many. Instead of thinking about hard limit of indexes…(A,B) for sorting optimization, for more complicated conditions you will need to use something like Trick …
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… one very important MySQL limitation – once MySQL runs into the interval range it will not use any further index parts. If you have A… to note this limitation only applies to interval ranges – for enumerated ranges MySQL will use both key parts. Hence if you change this… index order. It is very important to understand in which conditions index can be used to sort data together with restricting…
Post: Common MySQL traps webinar questions followup
January 17, 2013 By 1 Comment
… example on Amazon AWS? Hardware resources are usually limited in the cloud, and it is common … can still write conditions such as status = ‘payed’. The downside of ENUMs is that if you need …: Are these recommendations true of all versions of MySQL? If not which versions? The recommendations I made …
Post: Join me for 'MySQL 5.6: Advantages in a Nutshell.' Webinar. March 6 at 10 a.m. PST
March 4, 2013 By Leave a Comment
… of the advantages MySQL 5.6 offers. My focus is a practical one – to identify the conditions in which one …. You may find yourself wondering what advantages MySQL 5.6 provides and if the benefits exceed the effort of upgrading. … 22-25. Note: This code is available for a limited time only. * * *
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now… what 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…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…demo mview_table_alias: c mview_join_condition: USING (customer_id) mview_join_order: 999 *************************** 3… from the dashboard_customer_sales table. If you were paying attention to the … orders: mysql> delete -> from order_lines -> where order_id -> between 1 and 100 -> limit 500;…
Post: MySQL and IBM
April 6, 2009 By 17 Comments
… about Sun and IBM 
This is about MySQL. If you download latest 5.1.33 source…list of conditions. (b) Redistributions in binary form must reproduce this list of conditions, the … SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…LIMIT in the back end. In general this type of ORDER BY looks like: SELECT ….. WHERE [conditions] ORDER BY [sort] LIMIT…, even if it is quite fast: mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… This means many point queries, say for example if table1 yields 1000 rows then 1000 index …following changes were made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr…, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now let’s …
