One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times… need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though fixing… too much in this case throttling by having relatively short queries and introducing “sleeps” between them can be a good idea…
Comment: Multi Column indexes vs Index Merge
May 10, 2012 By 30 Comments
…mysql prod instance . The first SQL query executed in 0.03 secs whereas the second SQL query… LIMIT 505 ; …
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…_heap_table_size variable which allows you to limit size of MEMORY tables (the limit applies both to implicit and explicit ones… Percona Server you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length) from information… and fixed. This is where your MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory can help us…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
… this change I could bring optimization, and full query execution time to less than 50ms. Low values…found the following explanation from Timour Katchaounov in MySQL mailing list archives I have some recollection … value of min(number of tables, 7) essentially limiting search depth to no more than 7 …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… 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…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now let’s see how effective…_size set to 6M, the query time for MySQL 5.6 becomes approximately equal to that of MySQL 5.5. MariaDB 5.5… slow as compared to both MySQL 5.5 and MySQL 5.6. For MariaDB 5.5 the query time decreases when the join…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…, n_name, c_address, c_comment order by revenue desc LIMIT 20; In-memory workload Now let’s see how effective…, and the query time for MySQL 5.6 and MariaDB 5.5 jumps to ~11min (this is the query time for MySQL 5.5… when the correct query execution plan is not used, there is no difference in query times between MySQL 5.5 and MySQL 5.6…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… table1 ORDER BY a LIMIT 1699, 1 Can someone EXPLAIN if there is a rule that MySQL uses to determine whether to use index or filesort? On mysql manual I notice that the above query satisfies all conditions required for MySQL to use an index but…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 6 Comments
… ago, when I was just hired at MySQL. The assumptions about what are typical MySQL may have been true then in 2004… of MySQL‘s join optimizer is N!. The goal of this choice was to provide a conservative limit that guarantees that any query… the change to plan pruning in MySQL 5.6. Ideally, the server should analyze the query and should determine the search depth…
Comment: Introducing new type of benchmark
February 26, 2012 By 16 Comments
… it against your work load with your dataset with your queries; thats what I dont like. Additionally agile development is really… of those incremental schema changes or query changes instead of a tool to gage scaling limitations of server x. (Kinda a once… have a sharded datastore. A possible solution is to use mysql proxy and an alterate server with a snapshotted dataset and…
