title: why does the word "and" make MySQL full-text search so slow?
body: <p>I've written a CodeIgniter class to do a search of four db tables for some search string. These searches use <a href="https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html" rel="nofollow noreferrer">MySQL Natural Language Full-Text Search</a>. The SQL that actually performs the search is utilized as a prepared statement. This is what the SQL looks like, with the named parameter <strong>:clean_string</strong> getting replaced by the quoted string to be searched and the other named params being replaced by integers to weight the respective tables according to their importance:</p>
  
  <pre><code>SELECT c_i, c_t, seo_title, SUM(score) AS score
  FROM (
  
  SELECT c.id_code AS c_i, c.title AS c_t, c.seo_title, (MATCH(title) AGAINST (:clean_string IN NATURAL LANGUAGE MODE)) * :career_title_factor AS score, 'q_ct' AS qid
  FROM careers c
  WHERE MATCH(title) AGAINST (:clean_string IN NATURAL LANGUAGE MODE)
  
  UNION
  
  SELECT c.id_code AS c_i, c.title AS c_t, c.seo_title, AVG(MATCH(ts.task) AGAINST (:clean_string IN NATURAL LANGUAGE MODE)) * :career_task_statement_factor AS score, 'q_ts' AS qid
  FROM tasks ts, careers c
  WHERE ts.id_code = c.id_code
      AND MATCH(ts.task) AGAINST (:clean_string IN NATURAL LANGUAGE MODE)
  GROUP BY c_i
  
  UNION
  
  SELECT c.id_code AS c_i, c.title AS c_t, c.seo_title, AVG(MATCH(oat.alternate_title) AGAINST (:clean_string IN NATURAL LANGUAGE MODE)) * :career_alternate_title_factor AS score, 'q_at' AS qid
  FROM other_titles oat, careers c
  WHERE oat.id_code = c.id_code
      AND MATCH(oat.alternate_title) AGAINST (:clean_string IN NATURAL LANGUAGE MODE)
  GROUP BY c_i
  
  UNION
  
  SELECT c.id_code AS c_i, c.title AS c_t, c.seo_title, AVG(MATCH(od.description) AGAINST (:clean_string IN NATURAL LANGUAGE MODE)) * :career_occupation_data_factor AS score, 'q_od' AS qid
  FROM other_data od, careers c
  WHERE od.id_code = c.id_code
      AND MATCH(od.description) AGAINST (:clean_string IN NATURAL LANGUAGE MODE)
  GROUP BY c_i
  
  ) AS union_query
  GROUP BY c_i
  </code></pre>
  
  <p>This query runs nice and fast for most searches -- or even randomly generated nonsense strings. HOWEVER, it runs <em>extremely slow</em> when the word <strong>and</strong> appears in my search string. <strong>Why is this happening?</strong></p>
  
  <p>To demonstrate this, I have turned off MySQL query caching and run this test code:</p>
  
  <pre><code>public function test() {
      $new_elapsed = 0;
  
      $search_terms = array(
          "video games",
          "game video",
          "software",
          "nautical architect",
          "CEO",
          "pig and blanket machine",
          "art gallery",
          "rock and roll",
          "guitar god magic metal",
          "mott the hoople"
      );
  
  
      for($i=0; $i&lt;10; $i++) {
          $search = $search_terms[$i];
          echo "search term is $search\n";
  
          $start = microtime(true);
  
          $results_new = MY_search::search_new($this-&gt;db, $search);
          $new_elapsed = (microtime(true) - $start);
          echo "new has " . sizeof($results_new) . " matches\n";
          echo "new_elapsed: $new_elapsed\n";
  
          echo "--\n";
      }
  }
  </code></pre>
  
  <p>As you can see from the search results here, the two test queries with the word <strong>and</strong> in them run a LOT slower. Like a hundred times slower.</p>
  
  <pre><code>search term is video games
  new has 76 matches
  new_elapsed: 0.23601198196411
  --
  search term is game video
  new has 73 matches
  new_elapsed: 0.27281093597412
  --
  search term is software
  new has 124 matches
  new_elapsed: 0.41503596305847
  --
  search term is nautical architect
  new has 24 matches
  new_elapsed: 0.11621713638306
  --
  search term is CEO
  new has 2 matches
  new_elapsed: 0.012537002563477
  --
  search term is pig and blanket machine
  new has 1078 matches
  new_elapsed: 19.989203929901
  --
  search term is art gallery
  new has 53 matches
  new_elapsed: 0.19855809211731
  --
  search term is rock and roll
  new has 1077 matches
  new_elapsed: 16.845540046692
  --
  search term is guitar god magic metal
  new has 140 matches
  new_elapsed: 0.66281390190125
  --
  search term is mott the hoople
  new has 0 matches
  new_elapsed: 0.0023038387298584
  --
  </code></pre>
  
  <p>It is worth noting that "the" is included in the stop words and <strong>innodb_ft_min_token_size</strong> is set to 3.</p>
  
  <p>Why is this happening? I worry that "and" might be interpreted as some kind of operator by the full-text search engine. These longer search terms are unacceptable for my application. I am tempted to either a) add "and" to my stopwords or b) filter the word and from any search string in my code but I worry that this slow query problem might be triggered by other words.</p>
  
  <p>EDIT: Per Raymond comments, I have posted some explain statements here. I'm focusing on this one of the inner queries because it is <em>very slow</em> all by itself.</p>
  
  <pre><code>EXPLAIN SELECT c.id_code AS c_i, c.title AS c_t, c.seo_title, AVG(MATCH(ts.task) AGAINST ('pig and whistle' IN NATURAL LANGUAGE MODE)) * 3 AS score, 'q_ts' AS qid
  FROM tasks ts, careers c
  WHERE ts.id_code = c.id_code
      AND MATCH(ts.task) AGAINST ('pig and whistle' IN NATURAL LANGUAGE MODE)
  GROUP BY c_i
  </code></pre>
  
  <p>This yields:</p>
  
  <pre><code>id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
  1   SIMPLE  ts  fulltext    id_code,task    task    0   NULL    1   Using where; Using temporary; Using filesort
  1   SIMPLE  c   ALL     id_code     NULL    NULL    NULL    1110    Using where
  </code></pre>
  
  <p>Raymond suggested the query was not ANSI-compliant so I modified the group by clause to add the additional columns and tried again. It's still slow:</p>
  
  <pre><code>EXPLAIN SELECT c.id_code AS c_i, c.title AS c_t, c.seo_title, AVG(MATCH(ts.task) AGAINST ('pig and whistle' IN NATURAL LANGUAGE MODE)) * 3 AS score, 'q_ts' AS qid
  FROM tasks ts, careers c
  WHERE ts.id_code = c.id_code
      AND MATCH(ts.task) AGAINST ('pig and whistle' IN NATURAL LANGUAGE MODE)
  GROUP BY c_i, c_t, seo_title
  </code></pre>
  
  <p>The explain output is identical. </p>
  
  <p>If I explain the entire query (with the UNION statements et. al) I get a more elaborate result:</p>
  
  <pre><code>id  select_type         table   type    possible_keys   key     key_len     ref     rows    Extra   
  1   PRIMARY         &lt;derived2&gt;  ALL     NULL    NULL    NULL    NULL    3332    Using temporary; Using filesort
  2   DERIVED         c   fulltext    title   title   0   NULL    1   Using where
  3   UNION       ts  fulltext    id_code,task    task    0   NULL    1   Using where; Using temporary; Using filesort
  3   UNION       c   ALL     id_code     NULL    NULL    NULL    1110    Using where
  4   UNION       oat     fulltext    id_code,alternate_title     alternate_title     0   NULL    1   Using where; Using temporary; Using filesort
  4   UNION       c   ALL     id_code     NULL    NULL    NULL    1110    Using where
  5   UNION       od  fulltext    PRIMARY,description     description     0   NULL    1   Using where; Using temporary; Using filesort
  5   UNION       c   ALL     id_code     NULL    NULL    NULL    1110    Using where
  NULL    UNION RESULT        &lt;union2,3,4,5&gt;  ALL     NULL    NULL    NULL    NULL    NULL    Using temporary
  </code></pre>
  
  <p>EDIT 2: Raymond also asked for the table definitions. Here they are:</p>
  
  <pre><code>CREATE TABLE `careers` (
   `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
   `id_code` varchar(12) NOT NULL DEFAULT '',
   `title` varchar(250) NOT NULL DEFAULT '',
   `title_singular` varchar(250) NOT NULL DEFAULT '',
   `seo_title` varchar(150) NOT NULL,
   `cat_id` varchar(4) NOT NULL DEFAULT '',
   `occ_code` varchar(10) DEFAULT NULL,
   `forum_id` int(6) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   UNIQUE KEY `id_code` (`id_code`),
   KEY `cat_id` (`cat_id`),
   KEY `forum_id` (`forum_id`),
   FULLTEXT KEY `title` (`title`)
  ) ENGINE=MyISAM AUTO_INCREMENT=1111 DEFAULT CHARSET=latin1
  
  
  CREATE TABLE `tasks` (
   `id_code` char(10) NOT NULL,
   `task_id` decimal(8,0) NOT NULL,
   `task` varchar(1000) NOT NULL,
   `task_type` varchar(12) DEFAULT NULL,
   `incumbents_responding` decimal(4,0) DEFAULT NULL,
   `date_updated` date NOT NULL,
   `domain_source` varchar(30) NOT NULL,
   PRIMARY KEY (`task_id`),
   KEY `id_code` (`id_code`),
   FULLTEXT KEY `task` (`task`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  
  
  CREATE TABLE `other_titles` (
   `id_code` char(10) NOT NULL,
   `alternate_title` varchar(150) NOT NULL,
   `short_title` varchar(150) DEFAULT NULL,
   `sources` varchar(50) NOT NULL,
   KEY `id_code` (`id_code`),
   FULLTEXT KEY `alternate_title` (`alternate_title`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  
  
  
  CREATE TABLE `other_data` (
   `id_code` char(10) NOT NULL,
   `title` varchar(150) NOT NULL,
   `description` varchar(1000) NOT NULL,
   PRIMARY KEY (`id_code`),
   FULLTEXT KEY `description` (`description`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>