If you use functions on the ‘left hand side’ of a where clause, you ignore any indexes. “and left(a, 10) = ’2008-10-29′” should be “and ’2008-10-29′ = left(a, 10)”
Post: Finally. How to verify if all MySQL records were recovered
April 25, 2013 By Leave a Comment
… to know how many records an InnoDB page stores. The index page has a header PAGE_N_RECS – this is a… in the page: ./constraints_parser -5f pages-actor/FIL_PAGE_INDEX/0-1599/00000000-00000003.page -V … Checking a page Infimum… it will print “Leaf page: NO” and it can be ignored.
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… single column indexes (by hinting optimizer to ignore combined index) mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index (combined) where… it would do the index merge: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index (combined) where i1=50…
Post: check-unused-keys: A tool to interact with INDEX_STATISTICS
June 26, 2009 By 10 Comments
… host –ignore-databases Comma-separated list of databases to ignore –ignore-indexes Comma-separated list of indexes to ignore db_name.tbl_name.index_name –ignore-tables Comma-separated list of tables to ignore db…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…-optimize-keys ignores indexes on AUTO_INCREMENT columns, because they must be indexed, so it is impossible to temporarily drop the corresponding index; mysqldump –innodb-optimize-keys ignores the first UNIQUE index on non…
Post: Indexes in MySQL
June 2, 2006 By 29 Comments
… index access and with table scan: SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1 – 410 ms SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX… will use index. Execution time: SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1 – 1200 ms SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX (ID1) WHERE ID1=1 – 260 ms That is table scan is faster by 4.6 times. Why does MySQL choose index…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
… set (35.96 sec) mysql> select count(name) from testr ignore key (has_something) where has_something=0; +————-+ | count(name) | +————-+ | 18001245…) from testr ignore key (has_something) where has_something=0; 1 row in set (10.62 sec) query with index is still… testr ignore key (has_something) where has_something=0; 1 row in set (10.51 sec) Still query without index is faster…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… is derived (but not the ratio itself, which you should ignore). Read on for the details. In this article, I will… of tuning by ratio, and told you to ignore the ratio and in fact, ignore Key_read_requests altogether. I’ve explained… that is large enough to hold your working set — the index blocks that are frequently used. How large is that? This…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable… database server with b-tree indexes. And there are a bunch of subtleties, but I will also ignore those for the sake…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… UNSIGNED NOT NULL, update_time DATETIME NOT NULL, …. INDEX `uid` (uid, update_time), INDEX `bar` (some_other_columns) …. ) ENGINE=InnoDB; When he… to use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE INDEX(uid) and we got…
