Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
    
With the full_text_search-table, how will you identify the correct result with the correct type and id? –  Oskar Persson Aug 7 '13 at 21:49
1  
I edited my question. –  Arash Mousavi Aug 8 '13 at 8:20
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
1  
I have read your book SQL Antipatterns: Avoiding the Pitfalls of Database Programming. In "Polymorphic Associations" section you said in MySQL we can't have polymorphic association, in I want to have foreign key for this table I should have "a Common Super-Table",the master_id is the id generated by this common super-table. –  Arash Mousavi Aug 10 '13 at 12:00
1  
Then why not use that master_id as the primary key in each of the media tables, like the examples I show in my book? :-) The good reason to do that is the fact that primary key lookups are more efficient than secondary key lookups in the InnoDB storage engine. –  Bill Karwin Aug 10 '13 at 16:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.