Mysql search varchar column strategy

MySQLmysql-5.7

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.

Best Answer

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.

Related Solutions

Mysql – Need some expert eyes to look at the schema

Good start so far. Here are some notes:

a movie can be released on different dates in different cities
please add your primary keys, so we can see them
are images for movies? people? why no order column?
movies schedules are more complex than you've got so far. You will want to calculate show times based on some recurrence rules (see stackoverflow for recurrence posts)
what is 'location' in theatres? the address?

Regarding your questions at the bottom, don't loop. You can almost always do everything using SQL and its set logic. Make sure you understand joins:

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Mysql – Joining two Mysql VIEWs takes a very long time

Using FULLTEXT indexes has to be handled with great care. Why ? While FULLTEXT index searches do work, the MySQL Query optimizer tends to suggest full table scans if you do not express the query properly.

Let's take your query and look for 'tom'

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE c.castName LIKE '%tom%';

This joins everything. Then, it scans the resulting temp table and returns every row with tom.

Let's implement the FULLTEXT method

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

I can almost assure you that a full table scan will occur during the join because I have experienced it hundreds of times:

Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Jul 18, 2012 : Why full-text-search returns less rows than LIKE

I started writing posts about FULLTEXT and query refactoring ever since I saw a post from webmasterworld.com entitled MySQL Match Against + a very expensive join. How do I optimize this?

Given the information I just mentioned let's take your query and refactor it to handle the FULLTEXT and stilldo a good join.

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

First, apply the MATCH ... AGAINST operator to cast_movie and return ImdbId and other fields

SELECT ImdbId,movieName,castName,movieImdbId FROM cast_movie
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE)

Make that a subquery to provide keys to join and retrieve data from film_info

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM
(
    SELECT ImdbId,movieName,castName,movieImdbId FROM cast_movie
    WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE)
) as c JOIN film_info as f ON c.ImdbId = f.ImdbId;

This should produce a better result.

If your tables use InnoDB Storage Engine, remember to tune the InnoDB Fulltext options. Any fulltext option that does not start with innodb_ is meant for MyISAM (See my post MySQL FullText search on string shorter than 3 chars returns no rows)

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Need some expert eyes to look at the schema

Mysql – Joining two Mysql VIEWs takes a very long time

Related Question