… only actual rows which are found and returned up to the top level MySQL part for processing are counted the Rows_examined remains zero. It looks… because the access type to the tables is “const” MySQL does not count it as access to two tables. In case of…: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; This only sends 2 rows while scanning 10 million, while we…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
… of values are returned in 75 pct cases…zero: mysql> set global innodb_max_dirty_pages_pct=0; Query OK, 0 rows…mysql# /etc/init.d/mysql restart After the restart all InnoDB tables are reachable: mysql> select count(*) from sbtest; +———-+ | count(*) | +———-+ | 1000000 | +———-+ 1 row…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
…MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or rather return top 10 rows…MySQL only counts completely sorted rows in Sort_rows variable. In this case 1.000.000 of rows were partially sorted but only 10 rows… large enough so there is zero sort_merge_passes may not …
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… come first in an index. This is not specific to MySQL, it’s generally applicable to any database server with b… sake of simplicity. Let’s start with this query, which returns zero rows but does a full table scan. EXPLAIN says there are…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…mysql> SELECT COUNT(*) FROM test_table; — 3 Tickets Used +———-+ | COUNT(*) | +———-+ | 3 | +———-+ 1 row in set (0.00 sec) mysql…row returned UPDATE: 1 ticket per row examined + 1 ticket per row updated DELETE: 1 ticket per row examined + 1 ticket per row deleted ALTER: (2 * rows…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
…MySQL configuration, we … join buffer is allocated to cache rows from each table in a…_full_join, and you want zero of them. Anyway, back to…thd,JOIN_TAB *tables,uint table_count) 14185 { … snip … 14268 cache->length=…_malloc(size,MYF(0)))) 14273 DBUG_RETURN(1); /* Don’t use cache */ /* purecov: …
