FullText against keywords data base

Anon

What’s the best way to construct a search engine in Mysql over more than 40 000 news articles? Using the build-in fulltext indexing (and match against) or building a keywords data base to search in before?

van__

Hate to break it to you, but for something advanced like a search engine, MySQL is probably not what you want to be using.

I'd suggest getting prostGres.
It's not as fast as MySQL in most areas (Well, atleast according to the benchmarks at MySQL.org go figure). But, for that little loss of speed, you get much more power such as subselects, views and real temporary tables.

Plus, postGres supports more user connections and doesn't fall apart with 100+ users querying simultanously.

Now, I do like MySQL but I also like using the right tool for the job.

For a large sale database with a table or tables that will contain over 40,000 records, I would suggest breaking that down some. But not by 'keywords' that's redundant, because you'll wind up with millions of keywords or not enough keywords. What words are keys for an article and which aren't?

So you are left with fulltext searches, but I recommend searching by catagory with "all catagories" as the last option on the list.

So, you could have 'world events', 'financial news', 'sports news' etc
and then have a catagory field for each article (or better yet, a table for each catagory) that will help speed up your full text searches.

Another thing you could do is use dates.
Have the default be within 3 months.
That will allow the database to ignore rows that are over 3 months old and therefor, speed things up a bit. You can allow the users to have an advanced search page where they can specify longer or shorter periods of time as well.

There may be other items to latch onto as well to limit the number of records searched, but that is up to you and the data you have on hand.

Good luck.

Anon

Dear Van,

Thanks a lot for yours nice and clear comments.
I never worked with Postgres…. I know, they told me it was a good DBMS….

But it seems you indicated to use something like fulltext with some conditions (categories and dates…). Well it’s a very good idea.

Did you reed “Slapping together a search engine for your database is easy with PHP and MySQL” article on this site? It confused me… At the end I don’t know what’s the best method.

If the fulltext method is quicker, why so few information about it? I found a nice article on http://www.evolt.org but that’s the only one… is there more info (not official Mysql) on the net? Is that method proven?

Thanks again Van!

Karim,

van__

Thank you for getting me to read that article, I've seen it floating around there for awhile and never bothered to read it.

Ignore that article, it's crap.

First off: he's using BLOB to store text!
WTF?!?!?!?!

BLOB should be data we never want to interpret. Binary Data such as images, flash files, .pdf's and other binary formats or other data that may be harmfull and corruptive to the database if it ever tried to interpret it.

All databases have a TEXT type field.
Not varchar or char, but TEXT, for long lengthy lines of text text text!
Why not use that.

He opens his article with how searching in his blob field returns no meaningful results,
well... considering that the database wont ever try to interpret the data in a BLOB type, it's no wonder.

Secondly, this gem:

$filtered = trim($filtered);
$filtered = addslashes($filtered);
$querywords = ereg_replace(",","",$filtered);
$querywords = ereg_replace(" ",",",$querywords);
$querywords = ereg_replace("\?","",$querywords);
$querywords = ereg_replace("(","",$querywords);
$querywords = ereg_replace(")","",$querywords);
$querywords = ereg_replace(".","",$querywords);
$querywords = ereg_replace(",","','",$querywords);
$querywords = ereg_replace("^{","'",$querywords);}
$querywords = ereg_replace("$","'",$querywords);

And how many eregs do we use for what?!
1st thing wrong is just how wasteful that is in calling the same function repeatedly to do so little, a newbie should look at that and understand that is inneficient.

And ereg is a poor performer compared to preg functions.

he could have done this:

$patterns = array('/?/','/(/','/)/', ... );
$replace = array ('/'/','','', ... );
$querywords = preg_replace($patterns,$replace,addslashes(trim($filtered)));

...would have been far more efficient.

Also, that lil list of 'noisewords'
looks to me that could lead to certain words being ommited from a search that could be handy to have... like 'bad' for instance
what if you wanted to search for badmitten, bad mojo, bad boy?

The guy doesn't get it and his method would lead to the programmer or admin enslaving themselves to constantly tuning and tweaking words and rules of which are noise and which are not.

Bad scene all around.

In short, don't believe everything you read.