… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… speed up accesses to database. Some people would also remember if indexes are helpful or not depends on index selectivity -… by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count(pad)…
Post: UDF -vs- MySQL Stored Function
September 6, 2010 By 6 Comments
… minutes. Then we added another MySQL function for different set of attributes and indexing speed went from 16 minutes to 26… speed I could even scan the whole table of 7 million records: mysql> select count(*) from paintings where title != ltrim_junk(title); +———-+ | count(*) | +———-+ | …
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
…speed stays about the same: mysql> select count(*) from m3; +———-+ | count(*) | +———-+ | 10476840 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from m3 where i>0; +———-+ | count…Data_length: 685609952 Max_data_length: 948528873 Index_length: 0 Data_free: 0 Auto_…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
…`) ) mysql> select count(*) from dim; +———-+ | count(*) | +———-+ | 30720 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact where i select count(*) from fact left…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…Data_length: 100253696 Max_data_length: 0 Index_length: 128974848 Data_free: 0 Auto_…mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select count(i) from sample for update; +———-+ | count(i) | +———-+ | … pretty old box, which means locking speed will be few millions per second …
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
…selective it is likely it will need to scan less than 100 rows to generate result. The speed…selective using index scan is much better idea. Until MySQL is able to handle this you will have to use force index hint. The other problem you may have however is calculating count of matching…
Post: Learning about MySQL Table Fragmentation
May 1, 2008 By 19 Comments
…. To do it I run the query: “SELECT count(*) FROM tbl WHERE non_idx_col=0″ … scan – running count(*) without where clause may pick to scan some small index instead. If your …MySQL becoming CPU bound if IO subsystem is too fast. In this case however I saw neither – The vmstat showed read speed…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… are in MySQL? I just ran a quick micro-benchmark to compare the speed of a …mysql> delimiter // mysql> create function speaks_english(c char(3)) returns integer deterministic > begin > declare res int; > select count(…The query can’t be optimized to use indexes, and the stored function is opaque to …
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
…`c` (`c`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; select count(t1.pad),count(t2.pad) from t1,t1 …index and generally highly optimized Innodb builds hash indexes which helps to speed up lookup by indexes by passing BTREE index… the index, so there is no need for MySQL to request next row matching index …
