MySQL Searching with indexes

Question

I recently discovered how to benefit from the information EXPLAIN provides for SQL and I figured I should run all my queries through an EXPLAIN clause to see if I have unoptimized stuff. Turns out I have and I don't know what to do about it..

If I EXPLAIN this query

SELECT `id`,`username`,`first_name`,`last_name` 
FROM `users` 
WHERE (`username` LIKE '%kelon%' 
  OR `email` LIKE '%kelon%' 
  OR `first_name` LIKE '%kelon%' 
  OR `last_name` LIKE '%kelon%' 
  OR `facebook` LIKE '%kelon%' 
  OR `twitter` LIKE '%kelon%' 
  OR `skype` LIKE '%kelon%')

I get ALL for type, which is obviously the worst scenario, and I get NULL for possible_keys even though I have indexes on all of the columns. How can I optimize this messy query so that mysql wouldn't run away in case of a few million records?

[mysql like performance boost](http://stackoverflow.com/questions/2481528/mysql-like-performance-boost), [Increase speed of MySQL LIKE query?](http://stackoverflow.com/questions/9717190/increase-speed-of-mysql-like-query) — Uours, Oct 10 '13 at 22:20
@Uours well I can't have a constant prefix because that's data inputted from the user that I'm searching upon and I can't have fulltext searches because InnoDB doesn't support fulltext. Does that mean that there's nothing that could be done? — php_nub_qq, Oct 10 '13 at 22:22
[Fulltext Search with InnoDB](http://stackoverflow.com/questions/1381186/fulltext-search-with-innodb) — Uours, Oct 10 '13 at 22:26
@Uours unfortunately my hosting provider supports version 5.5.something `QQ` — php_nub_qq, Oct 10 '13 at 22:41
This is never going to perform well when using `LIKE` searches. See my presentation [Full Text Search Throwdown](http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql). — Bill Karwin, Oct 10 '13 at 23:31

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

Seems to me the answer could be to have FULLTEXT indexes .

But considering your table is currently InnoDB and that your hosting provider currently supports MySQL version 5.5 , I can only think of these options :

Switch that table to MyISAM
See if external full text search engine is an option : Lucene , Sphinx , Xapian etc.
Change to hosting provider that supports MySQL 5.6.4 or higher
Wait for your hosting provider to upgrade MySQL to 5.6.4 or higher

You can also try this to see if it makes any difference in good way :

SELECT `id`,`username`,`first_name`,`last_name` 
FROM `users` 
WHERE (
        `username` LIKE 'kelon%' 
    OR  `email` LIKE 'kelon%' 
    OR  `first_name` LIKE 'kelon%' 
    OR  `last_name` LIKE 'kelon%' 
    OR  `facebook` LIKE 'kelon%' 
    OR  `twitter` LIKE 'kelon%' 
    OR  `skype` LIKE 'kelon%'

    OR  `username` LIKE '%kelon%' 
    OR  `email` LIKE '%kelon%' 
    OR  `first_name` LIKE '%kelon%' 
    OR  `last_name` LIKE '%kelon%' 
    OR  `facebook` LIKE '%kelon%' 
    OR  `twitter` LIKE '%kelon%' 
    OR  `skype` LIKE '%kelon%'
)

Links :
What to do with MySQL Full Text Search while migrating to Innodb ?
What are the main differences between InnoDB and MyISAM ?

MySQL Searching with indexes

1 Answers1