… that every query is reading approximately 38.53 distinct InnoDB pages …Tables # SHOW TABLE STATUS FROM `wp_blog_one ` LIKE ‘wp_options’\G # SHOW CREATE TABLE `wp_blog_one `.`wp_options`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT…underlying tables involved and the query execution plan used by MySQL. The…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…approximately 4.5M rows, and an EXPLAIN produced the following execution plan: *************************** 1. row *************************** id: 1 select_type: SIMPLE table…status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…status | OK | +——–+———-+———-+——————————————————————-+ 2 rows in set (2 min 57.65 sec) mysql> SHOW TABLE STATUS…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… 29 air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG …data. Before loading, there was approximately 23GB of data. After loading,…LIMIT 10; The next queries show how performance is improved … It is currently approaching RC status. If you decide to test …
Comment: COUNT(*) for Innodb Tables
July 19, 2012 By 32 Comments
… get approximate number of rows as fast in Innodb as in myisam tables using “SHOW TABLE STATUS” command it will varry from…Checksum: NULL Create_options: Comment: customer table 1 row in set (0.00 sec) mysql> select count(*) from customers *************************** 1. row …
Comment: Finding out largest tables on MySQL Server
October 6, 2008 By 27 Comments
If I check mysql reference show table status seems to be very inaccurate if we take it for …. Is your SQL statement keying off the same values that show table status is? Rows The number of rows. Some storage engines, such… an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…MySQL currently allows you to see many per-session statistics for operations with SHOW SESSION STATUS…the query. This is actually an approximation based on a small hash …table: No Disk_tmp_table: No # Filesort: No Disk_filesort: No Merge_passes: 0 # No InnoDB statistics available for this query SELECT…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…mysql> select count(distinct c) from antest; +——————-+ | count(distinct c) | +——————-+ | 101 | +——————-+ 1 row in set (0.36 sec) mysql> select…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… of approximately 1500 to … So, MySQL checks …PRIMARY’ (root@localhost) [test]> show status like ‘handler%’; *** some rows omitted *** +—————————-+——-+ |… in our table: (root@localhost) [test]> select * from update_test; +————+———-+———+———————+…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table…MySQL 5.6 becomes approximately equal to that of MySQL 5.5. MariaDB 5.5 is quite slow as compared to both MySQL…status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL…, which shows that…
