…small buffer pool and a very large number of rows from table2 that do not fit …, to see the difference in numbers. Benchmark results For the purpose of this benchmark, I have …set to 6M and read_rnd_buffer_size set to 6M, the query time for MySQL 5.6 becomes approximately equal to that of MySQL…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…number of these point primary key lookups will be depend on the number of rows…in numbers. Benchmark results For the purpose of…in MariaDB 5.5 and not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
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… new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Power of MySQL Storage Engines
March 5, 2007 By 3 Comments
… paths dramatically. I know number of projects being interested in special techniques applying to optimizes certain types of queries with MySQL, it is not easy… is implemented in rather hackish way. Same applies to checking number of rows in result set and LIMIT implementation – these can optimized but not by use of MySQL Storage…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
… log record) is the number of rows which MySQL used to generate result set. Comparing this number with number of rows query actually accessed we can guess…of MySQL 5.0 MySQL will materialize the subquery in the from clause fully and so “use” all rows in result set while in reality only fraction of…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… of rows to perform group by so for large number of rows you’ve simply got to use different solutions, such as summary table. With MySQL… works up to certain number of rows in result set, with results set of 100.000+ it becomes pretty inefficient. There is surely SQL_CALC_FOUND_ROWS but it can…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 53 Comments
…two separate queries: one – to get a result set, another – to count total number of rows. In this post I’ll try to check… 1 row in set (0.00 sec) Here is why our count was much faster – MySQL accessed our table data when calculated result set …
Post: Reality of Innodb Caching
April 21, 2011 By 8 Comments
…set number of random IDs which are repeatedly selected in random order, which would illustrate data set with some randomly distributed “hot” rows. I read every row in…can show better results in such benchmark. Falcon with plans for row cache would fair better, so I would expect better results with …
Post: Handling big result sets
June 26, 2006 By 13 Comments
… lot of rows on client side. Usual way is send query via mysql_query and than handle the result in loop mysql…, $n35); /* store result */ $stmt->store_result(); while ($stmt->fetch()) { } printf(“Number of rows: %d.\n”, $stmt->num_rows); /* free result */ $stmt->free_result(); /* close statement */ $…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
…_len: 8 ref: const,const rows: 296338 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from goods force… all result set by price. This will be good choice if seller_id is selective, if it is not as in this case MySQL… estimated million of rows to analyze but we got rid of filesort so MySQL can stop as soon as 10 rows are sent. In this case…
