Mysql – Full text search through many tables in MySQL

database-designfull-text-searchMySQLnosqlrdbms

We have high traffic NEWS websites, I want to add a feature that every user can search through over all content of site, such as news, polls, comments, galleries,etc . Each of contents type has its own table(s).

I decided to create a table that holds all of contents from all types:

CREATE TABLE full_text_search
(
    master_id INT NOT NULL,
    content_text TEXT NOT NULL,
    PRIMARY KEY ( master_id )
);

I generate a unique number as master_id for each content of all types to identify each content_text in full_text_search table.

for example:

News table:
+----+-------------+---------+---------+----------+------------+
| id | news_title  | lead    | subtitle|  content | master_id  |
+----+-------------+---------+---------+----------+------------+
|  1 |  sometitle  |some lead| subtitle|content 1 |     3      |
|  2 |  some title |some lead| subtitle|content 2 |     5      |
+----+-------------+---------+---------+----------+------------+

article table:
+----+-------------+---------+------------------+---------+------------+
| id | title       | author  | short description| content | master_id  |
+----+-------------+---------+------------------+---------+------------+
|  1 |  sometitle  | someone | very short desc  |content1 |     1      |
|  2 |  some title | otherone|  some short desc |content2 |     4      |
+----+-------------+---------+------------------+---------+------------+

As you can see master_id is unique between above tables. When ever a new content from each type inserted, also I should INSERT it in to full_text_search table.

QUESTIONS

  • For many inserts for a day(about 3000 from all types), is it a good solution or it is anti pattern?
  • Is it better choice if I separate this table from my other tables, and put it in any other DB such as other RDBMS or NoSQLs?
  • Any other solutions?

Best Answer

Sure, it's fine to copy the searchable content to your full_text_search table.

MySQL supports FULLTEXT indexes only in the MyISAM storage engine (until MySQL 5.6, but fulltext in InnoDB in MySQL 5.6 still seems a little unstable). So you can store your canonical data in InnoDB for safety, and a copy in MyISAM for indexing. MyISAM is susceptible to data corruption, but if it's only a copy then you just need to repopulate the MyISAM table if it ever gets corrupted.

Your use of master_id as distinct from the primary key of each table is a little strange. Why not use the primary key, and add another column to your full_text_search table for the type of content?

CREATE TABLE full_text_search
(
    id INT NOT NULL,
    content_type ENUM('news','polls','comments','galleries','articles') NOT NULL,
    content_text TEXT NOT NULL,
    PRIMARY KEY ( id, content_type )
);

full_text_search table:
+----+--------------+---------------+
| id | content_type | content_text  |
+----+--------------+---------------+
|  1 |         news |     ...       |
|  1 |     articles |     ...       |
|  2 |     articles |     ...       |
+----+--------------+---------------+

Another option is to create a fulltext search index in another specialized technology such as Sphinx Search or Apache Solr. But the same pattern would be useful -- store the primary key field and a field for the type of content.

Related Question