php - Tag based searching with MySQL -

i want write tag based search engine in mysql, don't know how pleasant result.

i used like, stored on 18k keywords in database, it's pretty slow.

what got table this:

id(int, primary key) article_cloud(text) keyword(varchar(40), fulltext index)

so store 1 keyword per row , save refering article numbers in article_cloud.

i tried match() against() stuff, works fine long user types in whole keyword. want suggest search, there relevant articles popping up, while user typing. still need similar statement like, faster. , have no idea do.

maybe wrong concept of tag based searching. if know better one, please let me know. i'm fighting days , can't figure out satisfying solution. reading :)

match() against() / fulltext searching quick fix problem - schema makes no sense @ - surely there multiple keywords in each article? , using fulltext index on column contains single word rather dumb.

and save refering article numbers in article_cloud

no! storing multiple values in single column bad practice. when values keys table, it's mortal sin!

it looks you've got long journey ahead of create work efficiently; quickest route goal use google or yahoo's indexing services on own data. if want fix yourself....

see answer on creating search engine - keywords should in separate table n:1 relationship articles, primary key on keyword , article id, e.g.

create table article (     id integer not null autoincrement,     modified timestamp,     content text     ...     primary key (id) );  create table keyword (     word varchar(20),     article_id integer, /* references article.id     relevance float default 0.5, /* allow users record relevance of keyword article*/     primary key (word, article_id) );  create temporary table search (     word varchar(20),     primary key (word) ); 

then split words entered user, convert them consistent case (same used populating keyword table) , populate search table, find matches using....

select article.id, sum(keyword.relevance) article, keyword, search article.id=keyword.article_id , keyword.word=search.word group article_id order sum(keyword.relevance) desc limit 0,3 

it'll lot more efficient if can maintain list of words or rules words not use keywords (e.g. ignore words of 3 chars or less in mixed or lower case omit stuff 'a', 'to', 'was', 'and', 'he'...).