Best type of indexing when there is LIKE clause [duplicate]

by

Yep, what you have here is a database killer

A B-tree index can be used for column comparisons in expressions that
use the =, >, >=, <, <=, or BETWEEN operators. The index also can be
used for LIKE comparisons if the argument to LIKE is a constant string
that does not start with a wildcard character.

Source: http://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html

So that means your LIKE query cannot use the index and then you have two likes connected with an OR. If that’s not enough, you have thrown in a NOT IN comparison as well.

But fortunately, the second LIKE expression isn’t so bad, it doesn’t start with a wildcard. So your best hope is to create a composite index on usage_guidance, name

If you could post your SHOW CREATE TABLE and a few lines of sample data + the expected output, we might get an idea if there is a way to rewrite this query.

More Related Contents:

  1. How do I add indexes to MySQL tables?
  2. Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
  3. Is it possible to have function-based index in MySQL?
  4. When should I use a composite index?
  5. How do MySQL indexes work?
  6. Does MySQL index foreign key columns automatically?
  7. What does the KEY keyword mean?
  8. Is the primary key automatically indexed in MySQL?
  9. Difference between Key, Primary Key, Unique Key and Index in MySQL
  10. MySQL indexes – what are the best practices?
  11. How to see indexes for a database or table in MySQL?
  12. Speeding up row counting in MySQL
  13. What is cardinality in MySQL?
  14. How do I get MySQL to use an INDEX for view query?
  15. DOs and DONTs for Indexes [closed]
  16. MySQL performance: multiple tables vs. index on single table and partitions
  17. Mysql covering vs composite vs column index
  18. Is there any performance gain in indexing a boolean field?
  19. Does Order of Fields of Multi-Column Index in MySQL Matter
  20. MySQL load data infile – acceleration?
  21. Create an index on a huge MySQL production table without table locking
  22. MySQL Integer vs DateTime index
  23. Can MySQL use multiple indexes for a single query?
  24. How to fix MySql: index column size too large (Laravel migrate)
  25. MySQL distinction between e and é (e acute) – UNIQUE index
  26. MySQL optimizing INSERT speed being slowed down because of indices
  27. Would it help to add index to BIGINT column in MySQL?
  28. MySQL – why not index every field?
  29. Mysql performance on 6 million row table
  30. Workaround in mysql for partial Index or filtered Index?

Leave a Comment