… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… on which range condition is applied cannot be used for filtering records. For example, suppose you have a key defined as…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M… plans. a. Bad Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE nation ALL… where b. Good Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE orders range…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… by aggregating query log with mk-query-digest. When I filtered out only queries of this pattern and got a simplified…_next,QUICK_RANGE_SELECT::get_next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select,handle_select,execu te_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…MySQL will still print full number. Here is example: mysql> explain select * from lt limit 10; +—-+————-+——-+——-+—————+———+———+——+——+————-+ | id | select…
Post: Multi-Column IN clause - Unexpected MySQL Issue
April 4, 2008 By 20 Comments
…be some false positives but these are filtered out after reading the data so …mysql> explain select url from 124pages.124pages where url_crc=484036220 and url=”http://www.dell.com/”; +—-+————-+———-+——+—————+———+———+——-+——+————-+ | id | select…
Post: Beware of MySQL Data Truncation
February 7, 2009 By 4 Comments
… mysql> explain select count(*) from article_comment where article_id=4300000000; +—-+————-+—————–+——+—————+————+———+——-+——+————————–+ | id | select_type | table… my trivial example) and when filtering them out because value …
Post: Researching your MySQL table sizes
March 17, 2008 By 26 Comments
… of tables, rows, total data in index size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2… set (52.56 sec) Find the same data using some filter I often use similar queries to find space used by… | +——–+———+———+——-+————+———+ 1 row in set (0.03 sec) Find biggest databases SELECT count(*) tables, table_schema,concat(round(sum(table_rows)/1000000…
Post: Neat tricks for the MySQL command-line pager
June 23, 2008 By 10 Comments
… scary how predictable I am sometimes, isn’t it?) mysql> pager less mysql> show innodb status\G For big result sets, it’s…-filter manner. What sort of goodies can we think of here? mysql> pager mk-visual-explain PAGER set to ‘mk-visual-explain’ mysql> explain select * from sakila.film inner…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… the view and we add a filter condition on c1, the view is accessed quicky: mysql> select c1, count(*) from v1 where c1… view using a filter on c1, performance is not good. MySQL is scanning the entire table to produce the result: mysql> select * from v2…: mysql> select * from v2 where c1 = 100; Empty set (1.64 sec) You can see that MySQL is accessing millions of rows: mysql> explain select…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
…=MEMORY DEFAULT CHARSET=latin1 mysql> select count(*) from m; +———-+ | count(*) | +———-+ | 1047684 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from m where…) the filtering speed stays about the same: mysql> select count(*) from m3; +———-+ | count(*) | +———-+ | 10476840 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from…
