…to optimize queries for a star schema. A star schema presents some unique challenge to the database optimizer. … when the database is cold, but uses less CPU time when the database is hot. …set fits in memory, InnoDB almost always performs better, at least for this workload. Notes MySQL version used: …
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… wrong with a virtual-machine approach, but for the optimal production deployment you want two identical physical boxes…executed on the intermediate server, thus resulting in less binary log data being sent down to the… is brewing. Or if you watch your MySQL server’s memory usage and you see that it’s…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…MySQL 5.6 Makes Query Optimization Easier” for more tips on the 5.6 optimizer… one, which selects less amount of data (…memory). Also, I was testing, as I said before, index condition pushdown. Let’s have a look at the EXPLAIN output: mysql… over the ICP optimization: mysql> EXPLAIN SELECT role…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… query_cache_type=0 optimizer_switch=’index_condition_pushdown=on’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost… time. While for in-memory workload test, MySQL 5.5 takes 5x less time as compared to MySQL 5.6 and 13x less time as compared…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… 10; In-memory workload Now let’s see how effective are the join optimizations when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer… that the lowest query time is for MySQL 5.6 which takes 0.16s less as compared to MySQL 5.5 While with join…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with …total size or less to almost 100%. When your go from working set what fits in memory to the… BY B will use index (A,B) for sorting optimization, for more complicated conditions you will need to use…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… from using indexes. In fact even MySQL optimizer currently does not take it into account. For In memory workload index accesses might be faster… problem exists for all kinds of applications, however for OLTP applications with queries examining only few rows it is less of the problem…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
… even though in the read less than 1000 rows and doing it completely in memory. The plan optimizer picked was very good one… assume MySQL users do not join lots of tables (less than 7) when using 0 as default value would not impact them. For… compatibility. In MySQL 5.6 things are likely to get even better handling joins of many tables as optimizer heuristics are improved…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… with vanilla MySQL, you will need to aggregate your data. What you want to do is aggregate in ways that optimize the…/XtraDB tables… Optimize For I/O It is pretty much inevitable: if you do this kind of data processing in MySQL, you’re… able to avoid sharding for a long time and run on many fewer disk drives with much less memory, or even with 10…
Post: Profiling MySQL Memory Usage With Valgrind Massif
January 9, 2013 By 3 Comments
…MySQL memory usage; the Massif tool from Valgrind. An excerpt from the Massif manual page (Heap memory being simply the allotted pool of memory for…optimized or stripped binaries, which are optimized for…
