Since innoDB does not include full-text search, I decided to add a mirror table on the MyISAM engine with the FULLTEXT index, in addition to the main table. Example:

 CREATE TABLE IF NOT EXISTS `tab_search` ( id INT NOT NULL AUTO_INCREMENT, id_post INT( 33 ), text_post MEDIUMTEXT, FULLTEXT (`text_post`), PRIMARY KEY ( `id` ), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE IF NOT EXISTS `tab_main` ( id INT NOT NULL AUTO_INCREMENT, id_user INT( 33 ), id_post INT( 33 ), text_post MEDIUMTEXT, PRIMARY KEY ( `id` ), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

To output user data to the page I use the table tab_main . For search I use tab_search . The data in both tables are recorded simultaneously. The search query is as follows:

 SELECT tab_main.id_user, MATCH (tab_search.text_post) AGAINST ('$my_text' IN BOOLEAN MODE) AS relev FROM tab_main, tab_search WHERE tab_main.id_post=tab_search.id_post AND MATCH (tab_search.text_post) AGAINST ('$my_text' IN BOOLEAN MODE) ORDER BY relev DESC

As you can see from these examples, the text_post field text_post contained in both tables. This means that the same data must be stored on both tables. This also means that you will have to allocate twice as much memory to store this data. Question. Is it possible to completely remove the text_post field from the tab_search table, but remove it in such a way that the data on indexes are recorded in the table. Simply put, create a separate table with indexes only, and remove all unnecessary.

  • Isn't it easier to use special search engines for this? eg sphinxsearch - BOPOH
  • @ RAVEN, there is no desire to use third-party libraries. We try to be content with what we have. Actually this example works fine. Except that I would like to remove text_post from the tab_search table in order to save disk space. - StasHappy

1 answer 1

Beginning with MySQL version 5.6.4, full-text search is available in InnoDB.

An example of a query for creating a full_text_table table with a full-text idx index across the opening_line field:

 CREATE TABLE full_text_table ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, opening_line TEXT(500), author VARCHAR(200), title VARCHAR(200), FULLTEXT idx (opening_line) ) ENGINE=InnoDB;
  • Please try to publish detailed answers containing a specific example of the minimum solution, supplementing them with a link to the source. Answers –references (as well as comments) do not add knowledge to the Runet. - Nicolas Chabanovsky ♦