Re: 5.6 Adding new index, it does not get used
LIKE with a _leading_ wildcard can't make use of the index. Hence, it is more efficient to ignore the index and simply scan the entire table when you say
LIKE '%...'
FULLTEXT _may_ be better for your application. But remember that works with "words" and there are limitations on which "words" it will find. LIKE will not use FULLTEXT; you must reforumlate with MATCH (...) AGAINST (...)
VARCHAR(255) utf8 is at the 767 limit. INDEXing a VARCHAR(256) would have given you an error during CREATE TABLE.
Prefix indexing is rarely useful. And, if anything, it would have made the optimization worse.
ANALYZE TABLE on InnoDB is almost never needed.
In the future, please provide SHOW CREATE TABLE, and EXPLAIN SELECT.
Subject
Views
Written By
Posted
1533
July 15, 2014 03:00PM
791
July 16, 2014 09:13AM
753
July 16, 2014 11:13AM
Re: 5.6 Adding new index, it does not get used
744
July 17, 2014 03:49PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.