Mysql Improve Search Performance with wildcards (%

问题:

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?

回答1:

No, because MySQL will not be able to utilize the index when you have a leading wildcard. If you changed your LIKE to 'f%', then it would be able to use the index.

回答2:

No, Mysql will not use the index because LIKE argument (%f%) starts with the wildcard character %. If it starts with a constant, index will be used.

More info: 7.5.3. How MySQL Uses Indexes

回答3:

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.

回答4:

In my post here I describe, in detail, a technique that allows you to use the index with LIKE for fast %infix% search, at the cost of some extra storage:

https://stackoverflow.com/a/22531268/543814

As long as the strings are relatively small, the storage requirement is generally acceptable.

According to Google, the average e-mail address is 25 characters long. This increases your required storage by a factor 12.5 on average, and gives you fast indexed search in return. (See my post for the calculations.)

From my perspective, if you are storing 10'000 e-mail addresses, you should be fine storing (the equivalent of) about 100'000 e-mail addresses, too. If this is what it takes to allow you to use an index, that seems like an acceptable trade-off. Often, disk space is cheap, while non-indexed searches are unaffordable.

If you choose to take this approach, I suggest that you limit the input length of e-mail addresses to 64 characters. Those rare (or attacker) e-mail addresses of such length will require up to 32 times the usual storage. This gives you:

1. Protection against an attacker trying to flood your database, since these still aren't very impressive amounts of data.
2. The expectation that most e-mail addresses are not of this length anyway.

If you consider 64 characters too harsh a requirement, use 255 instead, for a worst-case storage increase factor of 127.5. Ridiculous? Possibly. Likely? No. Fast? Very.

回答5:

You will not be able to make it faster with LIKE just like everyone says (about the % at the beginning), but you can improve it a little by joining after you filter your people first.

SELECT *
  FROM (SELECT * 
          FROM `phppos_customers`
         WHERE `deleted` = 0
           AND  `email`  LIKE '%f%') `t_customers`
  JOIN `phppos_people` ON `t_customers`.`person_id`=`phppos_people`.`person_id`
 ORDER BY `email` asc

回答6:

Guys I am so happy that human brain is sometimes so smart that it can overcome apparently irresistible problems. I know how to outsmart mysql and enable the index search even when wild card searching from the left side. Just create a reversed column of your column (make it an index), reverse also the search string, and use the wildcard from right side which has the index support..

So if you have in db word "slibro" and you want search "%libro", created reversed column will contain "orbils" and search will be "orbil%".

If you like this my solution, please check my profile and send me some BTC, thanks :)

PS: But don't have the solution how to do fast full wildcard search "%x%" though :).