I can't find decent strategy for searching titles.
I have table for movies. Each movie has 5-10 alternative names that are stored in movie_names (id, name, name_clean, movie_id) table.
To normalize all names I use script that converts all names in ascii, removes whitespace, punctuation, etc. and place it in name_clean.
And on search I use
SELECT DISTINCT(movie_id) FROM movie_names WHERE `name_clean` LIKE '%f%' ORDER BY views DESC
And then I use ids and select with "where in" movies table.
Although this strategy works, but because of my name cleaning the search is very wide. I want to improve it without name cleaning, but wonder if I can improve it better.
For example I was thinking about search LIKE 'f%', but then I will need to explode every movie name in words.
And if I have movie name "My very fat big movie name" I need to make this much of additional rows:
"My very fat big movie name"
"very fat big movie name"
"fat big movie name"
"big movie name"
Also on people table I use query
SELECT * FROM people WHERE `name1` LIKE '%f%' OR name2 LIKE '%f%' ORDER BY views DESC
While searching movies the speed is okay, but it is very wide and selects too much results (search column is latin_swedish), with people table it's very slow (columns are utf8) and there are more rows.
How would you organize search in mysql.