0

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"
"movie name"
"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.

Improve this question
4
  • I would store the alternate names in a separate table that has a foreign key that relates back to the main movie table. For both the movie and people search I would have the front-end not start searching until a minimum of 2 or perhaps even 3 characters entered. That should really reduce the amount of hits while still giving you a decent result set.
    – Dave
    Commented Jun 15, 2018 at 19:26
  • thanks but this is already done, except people table (in people table i query columns)
    – jsHate
    Commented Jun 15, 2018 at 19:29
  • 1
    Maybe "12.9 Full-Text Search Functions" is interesting for you?
    – sticky bit
    Commented Jun 16, 2018 at 14:49
  • 1
    Only one column for the various names -- this will avoid the messy and slow ORs.
    – Rick James
    Commented Jun 19, 2018 at 2:46

1 Answer 1

Reset to default
0

thanks all for halp, especially sticky bit.

Here solution worked for me.

New table with id and name column with fulltext. I query with match against in boolean mode "query*". Works very well. No need for elastic.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.