1

Right now I have the following construct to find items with similar keywords:

CREATE TEMPORARY TABLE tmp (FULLTEXT INDEX (keywords)) ENGINE=MyISAM
    SELECT object_id, keywords FROM object_search_de;

SELECT object_id
    FROM tmp
    WHERE MATCH (keywords) AGAINST ('foo,bar') > 1.045;

DROP TEMPORARY TABLE tmp;

So, depending on the amount of overall records and the average size of the keyword field, this can get really slow (over 60 seconds execution time). My goal would be to be within 1 second for this task.

Alternatively to keywords comma separated in a TEXT field, I do also have an atomic keyord table (meaning two columns keyword and object_id, directly associating one keyword with an item).

Are there any alternatives or smooth solutions to achieving the same effect without resorting to a MyISAM mirror table?

Cobra_Fast
  • 15,671
  • 8
  • 57
  • 102
  • Why don't you just have a full text index on `object_search_de`? – Gordon Linoff Sep 16 '13 at 13:22
  • @GordonLinoff Because it's an `InnoDB` table and `InnoDB` does not support fulltext. – Cobra_Fast Sep 16 '13 at 13:26
  • I just found http://stackoverflow.com/questions/1381186/fulltext-search-with-innodb?rq=1 and am now aware that `InnoDB` supports fulltext as of version 5.6. However, I am not certain if updating the MySQL environment wouldn't break other things that have been built against earlier versions of MySQL. – Cobra_Fast Sep 16 '13 at 13:34
  • . . It does in more recent versions of MySQL. – Gordon Linoff Sep 16 '13 at 13:34

1 Answers1

0

First of all, do not create the table each time. You can create it once and use a trigger to insert/update/delete records or periodically (every hour for example) truncate and insert the records if you don't want to use triggers.

Alternatively, you can offload this task from MySQL and use Lucene/Solr or Sphinx.

Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89