… might want to look at Prepared_stmt_count to see how many prepared statements are …tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… 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: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…evaluated in following steps, without MRR: SELECT key_column, pk_column FROM …MySQL 5.6 code? As MRR was used in both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts…index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 16 Comments
… index mysql> select count(name) from testr force key (has_something) where has_something=0; +————-+ | count(name) | +————-+ | 18001245 | +————-+ 1 row in set (35.96 sec) mysql> select count… sec) Still query without index is faster. And finally for case with 20% rows with has_someting=0 mysql> select count(name) from testr…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
…=MyISAM DEFAULT CHARSET=latin1 mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(val) from fact; +————+ | count(val) | +————+ | 7216582 | +————+ 1…: mysql> select count(*) from fact where i explain select count(*) from fact where i select count(val) from fact where i explain select count(val) from fact where i select count…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…, unless index length increases dramatically queries which can use index can also use prefix of the new index are they …latin1 mysql> select count(*) from idxitest where a=5 and b=5; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1 row in set (0.69 sec) mysql> explain select count…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
… by the given user ? mysql> select count(*) from comments where user_id=5; +———-+ | count(*) | +———-+ | 1818 | +———-+ 1 row in set…rows: 1792695 Extra: Using index 2 rows in set (0.96 sec) mysql> select * from user_counts; +———+——+ | user_id | cnt | +———+——+ | …
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
…mysql> SELECT count(*) FROM Country; +———-+ | count(*) | +———-+ |Â Â Â Â Â 239 | +———-+ 1 row in set (0.00 sec) mysql> SELECT count(distinct(population)) FROM Country; +—————————–+ | count…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
…..100 selects about 1% of the table. The times for full table scan vs range scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count(pad) from large…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… pct FROM index_usage AS iu INNER JOIN ( SELECT query_id, db, tbl, SUM(cnt) AS total_cnt, COUNT(*) AS variations FROM index_usage… is query 7675136724153707161, anyway? mysql> select * from queries where query_id = 7675136724153707161\G *************************** 1. row *************************** query_id: 7675136724153707161 fingerprint: select * from wp_comments where… indexes, and for what queries? This time I’ll add LIMIT 2 to the query, because there are lots of them: mysql> SELECT…
