… the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which…_keys: role_id_note key: role_id_note key_len: 4 ref: const rows: 10259274 Extra: Using index…, using FORCE-like commands or optimizer_switch flags- we can disable ICP, but not “using index“. …
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… AUTO_INCREMENT PRIMARY KEY, uid INT UNSIGNED NOT NULL, update_time DATETIME NOT NULL, …. INDEX `uid` (uid, update_time), INDEX `bar` (some_other… try to force MySQL to use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE INDEX(uid) and…) NOT NULL AUTO_INCREMENT, `update_date` datetime NOT NULL, PRIMARY KEY (`i`), KEY `i` (`i`,`update_date`) ) ENGINE=InnoDB and then inserted…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual,… innodb_force_recoveryblogpercona….key_cache_age_thresholdblogpercona.commanual key_cache_block_sizeblogpercona.commanual key_cache_division_limitblogpercona.commanual Key_read_requestsblogpercona.commanual Key_readsblogpercona.commanual Key_write_requestsblogpercona.commanual Key…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…) NOT NULL, `val` char(10) NOT NULL, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1…to force index merge as you can do with index accesses in general. Note2 Q2/Q3 can’t use Index…cardinality even though MySQL can’t use index well. You’re right MySQL can’t and MySQL does not – Full…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
…MySQL, it’s best to show some execution plans on dummy data: EXPLAIN SELECT * FROM coordinates FORCE INDEX…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
…) Let’s check execution time with and without index mysql> select count(name) from testr force key (has_something) where has_something=0; +————-+ | count… without index is faster. And finally for case with 20% rows with has_someting=0 mysql> select count(name) from testr force key (has…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…indexes so MySQL Optimizer does not have to chose between better sort or better lookup or use FORCE INDEX to force it to use appropriate index…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
… about indexes.) … Notice in each SQL statement the clause: FORCE INDEX(`guest_language`) The tool has chosen to use the guest_language index instead of the PRIMARY key. This is… there are any bugs in the tool, problems with the MySQL server (privileges, etc.), or other oddities, there is a good…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
…MySQL check WHERE clause while scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql> explain select * from table FORCE INDEX…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… Especially assuming that there are indexes other than the primary key, we can shrink the primary key‘s width: create … work by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and earlier, …But beware of falling into the trap of brute-forcing a solution that really needs to be solved …
