…EXPLAIN is lieing you you can use SHOW STATUS “Handler” statistics to see if number of operations match. EXPLAIN works for SELECT …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 …
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… that both MySQL 5.6 and MariaDB 5.5 are reporting high numbers for Innodb_buffer_pool_read_ahead which shows that the…
Post: Replaying database load with Percona Playback
October 25, 2012 By 9 Comments
…of your application. In the example I’m going to show how to meassure the impact of… –mysql-host=127.0.0.1 –mysql-user=root –mysql-schema=…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…
Post: Reality of Innodb Caching
April 21, 2011 By 8 Comments
… row size in this table is smaller but lets use this number for our math. For benchmark I’m using set number of random IDs which are repeatedly selected in random order, which would illustrate data set with some randomly distributed “hot” rows… should fit roughly 500K of rows 224 bytes in size. Lets see what Benchmark really shows: Testing 100 out of 10000000 keys 24.79…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… variation of the Log Sequence Number during a given period of time. Instead of manually looking for the right line in the output of SHOW ENGINE… sequence’ mysql> show engine innodb status\Gselect sleep(60);show engine innodb status\G Log sequence number 380166807992 1 row in set (0.41 sec) 1 row in…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… the table is locked in “IS” mode and there are number of row level locks in “S” mode. What does it mean ? Well… – SHOW INNODB STATUS does not know anything about MySQL level locks, so table locked on MySQL level with LOCK TABLES will not show up…. Explicitly locking tables on MySQL level will prevent tables from being accessed and will not show up in SHOW ENGINE INNODB STATUS. It…
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
… This shows there is surely the problem with dynamic row format table with many columns. But is it because of large number of columns or… mysql [localhost] {msandbox} (test) > select max(t1+0) from t99v1; +———–+ | max(t1+0) | +———–+ | 0 | +———–+ 1 row in set (3.26 sec) mysql […
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…have a small buffer pool and a very large number of rows from table2 that do not fit into the …to that of MySQL 5.5. MariaDB 5.5 is quite slow as compared to both MySQL 5.5 and MySQL …by the high numbers of Innodb_buffer_pool_read_ahead when the buffers are sized appropriately, which shows that the …
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… table of numbers, and I want to sum them all up: mysql> show create table ex1; …row in set (0.00 sec) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql…
Post: Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
… but if you ask but rather only number of rows it needs to reach the LIMIT Sphinx mysql> select forum_id as f from… which means it will likely stabilize soon. Sphinx also offers number of extensions to the SQL which are helpful for search use… to pick within given group (like if you want to show most recent document, or most relevant) and others. You might…
