…, if it is not as in this case MySQL needs to sort a lot of rows to display only few. If we force… with estimated million of rows to analyze but we got rid of filesort so MySQL can stop as soon as 10 rows are sent. In… is calculating count of matching rows which may be even trickier to slow for complex searches which generate a lot of rows. Another interesting…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 5 Comments
…. The lack of these features in the MEMORY storage engine (also known as HEAP) in MySQL has been one of the long-standing… same for both row formats. (Speaking very strictly, the VM Peak value is not comparable due to slightly different number of rows inserted). In the second test we check if the dynamic row format delivers the memory savings…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… output of the query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in the output of EXPLAIN. Now this can be a real pain as MySQL uses “the number of rows” estimation to…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… which reduces number of passes MySQL had to perform and so number of row combinations really examined. LIMIT is not taken into account while estimating number of rows Even if you have LIMIT which restricts how many rows will be examined MySQL will still print full number. Here is example: mysql…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
… than number of rows returned. What I would like to see (for example as another slow query 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…
Post: Replaying database load with Percona Playback
October 25, 2012 By 9 Comments
… can replay the load: percona-playback –mysql-host=127.0.0.1 –mysql-user=root –mysql-schema=my_db –query-log-file=slow… total of 51 queries had errors. Expected 372462 rows, got 371851 (a difference of 611) Number of queries where number of rows differed: 324. That’s the report of… were slower A total of 51 queries had errors. Expected 372462 rows, got 371851 (a difference of 611) Number of queries where number of rows differed: 324. Much…
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
… for dynamic rows, however it is not very significant if number of columns is small. For large number of columns dynamic rows become very …mysql [localhost] {msandbox} (test) > select max(t1+0) from t99v1; +———–+ | max(t1+0) | +———–+ | 0 | +———–+ 1 row in set (3.26 sec) mysql […
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… made against base table, the number of these point primary key lookups will be depend on the number of rows that match the condition ‘key_column=x’. You can see that there are a lot of random primary… reading more rows than MySQL 5.5, as can be seen by the numbers reported for the status counter Innodb_rows_read. While MySQL 5…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… restrictions, such as row level locks. There is also a load aspect – “random arrivals” tell us the number of work system has to… #15 here is kernel and user CPU usage of MySQL process in 1/100 of the second. (This is pretty idle test system… IO (which you can see separately through number of IOPS) but it also can be row level locks, etc. The ratio between Wait…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… cases. Now In MySQL 5.1+ it is also possible to fix this problem for cases when number of rows in auto-increment batch insert is not known as we do not have to allocate sequential auto increment values if row… Google released so many MySQL patches so we can call patched version GoogleSQL. Among them there are number of patches to make Innodb…
