I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… numbers for the counter Handler_read_key. This is because of how status counter values are incremented when index lookup is performed… and Innodb_rows_read to be incremented by TWO. It does not actually mean that queries with MRR are performing badly. The interesting thing is that though both MariaDB and MySQL 5.6 are reporting high numbers for Innodb_rows_read…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… rows an index lookup is performed on the second table. This means many point queries, say for example if table1 yields 1000 rows… you have a small buffer pool and a very large number of rows from table2 that do not fit into the buffer… table t2, then selected rows from t1 would be read and then for all rows, index lookup would be performed on t2. Here…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
… even though in the read less than 1000 rows and doing it completely in memory. The plan … yet making it unusable to check the optimizer performance. Solution for this problem was to use set … automatic selection works – it picks value of min(number of tables, 7) essentially limiting search depth to …
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… rows (~1.2G ) and buffer pool 30G. The scripts and results are on Benchmark Wiki. If we look on performance …118218 COUNT_WRITE: 849692 SUM_NUMBER_OF_BYTES_READ: 1936883712 SUM_NUMBER_OF_BYTES_WRITE: 103557693440 *************************** 2. row *************************** FILE_NAME: /data/tachion…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… handle more than 1.000.000 (or select any other number) rows by some of the users. On other hand it is… here is small example with numbers. If you have your data fully in memory you could perform over 300.000 of random…. We’ll need to perform 30 millions of random row reads, which gives us 300.000 seconds with 100 rows/sec rate. So…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
… not the number of rows returned by the query but number of rows accessed by the query will most likely be defining query performance. Of course not all row accessed are created equal (such as full table scan row accesses may be…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… values we need to know total number of rows which matches our where clause. Most Simple Way Number One is to simply run… | 227 | +——-+—–+ 10001 rows in set (21.39 sec) This method indeed works great if you have relatively small number of groups which… well implemented inside MySQL. Why am I looking on reporting performance optimization ? It is for ClickAider project which is growing rapidly…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… *************************** 1. row *************************** cnt: 155187034 1 row in set (0.03 sec) Notice the difference in response time. Refresh method performance comparison … refreshable views, the last parameter is a transaction id number, which is usually obtained with flexviews.get_uow_id_…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… application which can perform search on many …indexing. Also note – number of records scanned…| possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——+—————+——+———+————-+——+———————————+ | 1 | SIMPLE | test | ref | …
