… optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’batched_key_access=on’ join_buffer_size=6M read_rnd_…the values for ‘Handler_read_key’, ‘Handler_read_next‘ and ‘Handler_read_rnd_next‘. MariaDB 5.5 with Hash Joins enabled is doing less Handler reads as compared to…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…Handler_read_key 508833 623738 622184 508913 507516 Handler_read_next 574320 574320 572889 574320 572889 Handler_read_rnd_next 136077 136094 136366 136163 136435 Innodb_buffer_pool_read…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 42250 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Handler_prepareblogpercona.commanual Handler_read_firstblogpercona.commanual Handler_read_keyblogpercona.commanual Handler_read_lastblogpercona.commanual Handler_read_nextblogpercona.commanual Handler_read_prevblogpercona.commanual Handler_read_rndblogpercona.commanual Handler_read_rnd…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…Handler_read%’; +———————–+——–+ | Variable_name | Value | +———————–+——–+ | Handler_read_first | 0 | | Handler_read_key | 3 | | Handler_read_next | 118181 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… 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 the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…’ ‘Com_optimize‘, ’0′ ‘…Handler_commit’, ’19092941′ ‘Handler_delete’, ’0′ ‘Handler_discover’, ’0′ ‘Handler_read_first’, ’1100773′ ‘Handler_read_key’, ’2491307870′ ‘Handler_read_next‘, ’1266206297′ ‘Handler_read_prev’, ’0′ ‘Handler_read_rnd‘, ’234159075′ ‘Handler_read_rnd_next‘, ’2358572961′ ‘Handler…
Post: How to estimate query completion time in MySQL
April 22, 2008 By 16 Comments
… customer’s site where a typical data-warehousing query needed optimization. It was a fact table joined to two dimension tables… -r -i 10 | grep Handler_read_rnd_next — ignore the first line of output… | Handler_read_rnd_next | 429224 | So the server was reading roughly 43K rows per second…
Comment: How much memory can MySQL use in the worst case?
February 19, 2009 By 17 Comments
… few: Innodb_buffer_pool_reads 12 Handler_read_rnd 2,448 k(this increases steady as the server runs) Handler_read_rnd_next 105 M(this increases steady… the queries that are to blame here or could i optimize the config for the server some….maybe use InnoDB since…
Comment: Why MySQL could be slow with large tables ?
November 14, 2008 By 141 Comments
I need to optimize mysql server to manage a big tables (now about 78Mb, … select query with ORDER BY, LIMIT and with paging — Handler_read_rnd 12 M Handler_read_rnd_next 4,483 M Created_tmp_disk_tables 5,270 Created_tmp_tables 5,274 Created_tmp_files 37 k Key_reads…
