is this the right way to design FTS?

[Shannon Fang]

Hi All,

I am designing a FTS system on a website based on mysql. My idea is like this. I will scan all articles on the site and generate an index, similar to the following index_table:

keyword article_id
kw1 1
kw1 2
kw1 3
kw2 2
kw2 3
kw2 4

While user search for an article contain kw1 and kw2, I will execute the following SQL:

select t1.article_id from index_table as t1 join index_table as t2 using(article_id) where t1.keyword="kw1" and t2.keyword="kw2"

It seems to work... However, my concerns are:

1) is it the right way to build a fts system? is there any easier or more extensible way (e.g. when user enter 10 keywords)?

2) if it is a good way, the index_table will become quite large, will mysql handle that with good performance?

Any comments or suggestions are greatly appreciated.

Thank you!

Shannon