… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… clarify what MySQL Query Cache is – I’ve seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query Cache is not… of the queries – in this case you can set query_cache_type to “DEMAND” and use only SQL_CACHE hint for queries which you…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… the query which performs differently between MySQL Server versions you should: Check Query Plan Run EXPLAIN to see if plans for the query are the same. Changing Query Execution… change your application by changing how query is written and provide the hints. Check Query Execution If query execution plan looks the same it…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… to old optimizer behavior with new MySQL Version. Make Cost Model Adjustable MySQL Optimizer looks at query plan in terms of disk IOs… MySQL has proper ways to execute the query. It may not always pick them right but at least it allows to get query…) as well as simply hints. Any way MySQL could possibly execute query should be possible to force with hints. In MySQL 4.0 this was…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into the query to change how query…
Post: Debugging sleeping connections with MySQL
February 8, 2007 By 28 Comments
…. Or may be you have several connections to MySQL server and right now running query which takes that long ? In any case… which that process is processing and so get few more hints on what may be happening or even get repeatable example… process are not only helpful to debug sleeping connections with MySQL but many other cases when you see web application locking…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… would unexpectedly fail. To give you couple of hints: Temporary Tables You can build query (with derived tables) which uses as many… create. Parse Tree The query is internally presented using parse tree inside MySQL which of course depends on query size which is controlled by max_allowed_packet. However some MySQL optimizations such…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… of swap space) like crazy. Why ? Because by default mysql_query uses mysql_store_result C library call and buffers all result set… workaround for this one is to use SQL_BUFFER_RESULT hint if you need to release table locks early. It comes… If you use buffered query the only real call you can get errors is when you run mysql_query , mysql_fetch_row simply reads…
Post: New SpecJAppServer results at MySQL and Sun.
November 12, 2008 By Leave a Comment
… useReadAheadInput=false useUnbufferedInput=false cacheServerConfiguration=true This gives you some hints what you can try for JDBC configuration. Some caching options…-output = FILE log-slow-queries=/tmp/mysql-slow.log long_query_time = 1 innodb_data_home_dir = /data/mysql/var innodb_data_file_path…
Comment: Multi Column indexes vs Index Merge
September 20, 2009 By 30 Comments
… same testing for MySQL 5.0.x instead of 5.1/5.4？ As in my website, the MySQL query optimizer in… is much better, and sometimes I have to add some query hints to help 5.1 using the right index. Thus, until…
