Mysql Improve Search Performance with wildcards (%%)

Question

Below is a query I use for searching a person by email

  SELECT *
    FROM phppos_customers
    JOIN phppos_people ON phppos_customers.person_id = phppos_people.person_id
   WHERE deleted = 0
     AND email LIKE '%f%'
ORDER BY email ASC

Will adding an index on "email" speed up the query?

Answer 1

Wildcarding the left side of a LIKE operation ensures that an index, if one exists on the email column, can not be used.

Full Text Search (FTS) is preferred syntax for finding strings within text via SQL. MySQL has native FTS functionality, using the MATCH/AGAINST syntax (Requires the table to use the MyISAM engine for v.5.5 and below. InnoDB FTS supported on v.5.6+):

  SELECT c.*, p.*
    FROM PHPPOS_CUSTOMERS c
    JOIN PHPPOS_PEOPLE p ON p.person_id = c..person_id
   WHERE deleted = 0
     AND MATCH(email) AGAINST('f')
ORDER BY email 

But there are third party FTS technology, such as Sphinx.