title: Efficiently filtering movies and genres (with spelling mistakes)
body: <ul>
  <li>I have a large database of movies (over 4000 movies) which I would like to filter by genre that I got from various shop XML's</li>
  <li>Every movie can have more than 1 genre so it is a many to many relationship of movies and genres</li>
  <li>every genre can have more than 1 name (different language, misspellings)</li>
  <li>every genre in its original form (with all the spelling mistakes) gets joined with a cross join table and then joined with the movies table</li>
  <li>every genre has a column which groups (or clusters) bad names and good names and another column to show which one is the one that I might want to output</li>
  <li>I want to filter the movie database and select movies from 1 or 2 genres no matter how the genres were spelled</li>
  </ul>
  
  <p>My tables are setup like this (removed some columns from <code>movies</code> for sake of brevity):</p>
  
  <pre><code>  CREATE TABLE `movies` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `title` varchar(255) NOT NULL DEFAULT '',
      `alias` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `alias` (`alias`),
      KEY `title` (`title`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    CREATE TABLE `movies_x_genre` (
      `movieid` int(11) NOT NULL,
      `genreid` int(11) unsigned NOT NULL,
      PRIMARY KEY (`movieid`,`genreid`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    CREATE TABLE `genre` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `genre` varchar(100) NOT NULL,
      `group` int(11) unsigned DEFAULT NULL,
      `type_id` tinyint(1) DEFAULT NULL,
      `valid` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      UNIQUE KEY `genre` (`genre`,`type_id`),
      KEY `idx_genre` (`genre`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <h2>Example:</h2>
  
  <p>I have the valid or invalid genres</p>
  
  <pre><code>  INSERT INTO `genre` (`id`,`genre`,`group`,`type_id`,`valid`) VALUES 
    (1,"Comedy",1,1,1),
    (2,"Comedies",1,1,0),
    (3,"Action",2,1,1),
    (4,"Acton",2,1,0);
  
    INSERT INTO `movie_x_genre` (`movieid`,`genreid`) VALUES 
    (1,1),
    (2,2),
    (1,3),
    (2,4);
  </code></pre>
  
  <h2>Explanation</h2>
  
  <p>The problem I am facing is that I store movie genres into the genres table in many languages and often variations of the genre in certain languages,
  every genre that has the same meaning, or at least the same meaning but in a different language has a common "group" id stored in the <code>group</code> column that was set manually,
  the movie gets joined with the genre that came with it and the genre gets stored in the database with a new id, if it is a new genre, this allows me to store the genre now and have it usable and fix it later if it should belong to a group because I simply cannot group genres manually every day.</p>
  
  <p>the language id of the genre is stored in type_id, while the <code>valid</code> column marks which variation of the genre name is the correct one.</p>
  
  <h2>Select query</h2>
  
  <p>When I run the below query, to select all the movies in the genre "comedy" no matter what language or variation</p>
  
  <p>When I select "Comedies" I want to select movie with id 1 and with id 2 because they are both comiedies, just written differently
  But select query is very slow, it takes around 0.5 s
  When I run the below part I see that the "Copying to tmp table" is taking very long</p>
  
  <pre><code>SET profiling = 1;
  SELECT SQL_NO_CACHE i.id,i.alias,i.title 
  FROM genre g
  INNER JOIN genre g2 ON g.`group`=g2.`group`
  INNER JOIN movies_x_genre x ON x.genreid=g.id
  INNER JOIN movies i ON i.id=x.movieid
  WHERE g2.`genre` = "comedy"
  GROUP BY i.id;
  SHOW profile;
  </code></pre>
  
  <p>I ran into this answer <a href="https://stackoverflow.com/questions/7532307/skip-copying-to-tmp-table-on-disk-mysql">skip copying to tmp table on disk mysql</a> and ran</p>
  
  <pre><code>SHOW VARIABLES LIKE '%tmp_table_size%';#1073741824 = 1GB
  SHOW VARIABLES LIKE '%max_heap_table_size%';#1073741824 = 1GB
  </code></pre>
  
  <p>I don't think I should increase these any more than that</p>
  
  <p>Why is the select so slow? Did I organize the tables right? Am I missing indexes? How should I have organized my tables if they are wrong? What is the most efficient way to store the information to filter the movies like that?</p>