Boolean or Not to Boolean?

Sharif

Ok so trying to build a good search script for my sites articles... doing well so far even though I am stuck near the end but I have a question... is it good to use Boolean full-text searching or not? What do you guys think?

Also, I keep getting this error in a query:

SELECT id, DATE_FORMAT(timestamp,'%m/%d/%Y at %l:%i:%s %p') AS datetime, title, article FROM news WHERE MATCH (title) AGAINST ('star' IN BOOLEAN MODE) ORDER BY timestamp DESC LIMIT -25, 25

The error is:
You have an error in your SQL syntax near 'BOOLEAN MODE) ORDER BY timestamp DESC LIMIT -25, 25' at line 1

any ideas?

tsinka

Hi,

which MySQL version do you have and which type is the table of (MyISAM,...) ?

Thomas

Sharif

The version is 3.23.58 and table type is MyISAM

tsinka

According to the manual you MySQL version is too old:

http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html

As of Version 4.0.1, MySQL can also perform boolean full-text searches using the IN BOOLEAN MODE modifier.

Thomas

Sharif

That is just great... I think I am definetly leaving my host now. Anyways, boolean or not boolean?

Sxooter

I use booleans all the time. If it's a data field where true / false fits, boolean is the way to go.

Sharif

Well would it be good to use on a search script that searches for articles with search terms in the title or the article?

Sxooter

Sure. It lets you build a pretty simple query:

select * from celebrities where dancer is true AND actor is true AND lname like 'Walken'

Or something like that.

Sharif

Actually aren't searches like this:

SELECT field FROM table MATCH (fields) AGAINST ('search terms' IN BOOLEAN MODE) ?

Sxooter

In MySQL I guess, but the SQL spec defines they work the way I had them. IFF you don't support any other databases then the MySQL syntax should work fine. You might want to check the later versions and see if it supports the SQL92 syntax.

But it looks like the stuff you're doing would be better served by using a full text search engine.

Sharif

The problem I am having is that I can't do boolean searches with full-text because my host is running an old version of mysql 🙁

Sxooter

I guess I'm not that familiar with MySQL's boolean mode FTS system. What does the "boolean" keyword get you?

Sharif

what do you mean?

Sxooter

Oh, nevermind. MySQL's use of the word Boolean here has nothing to do with the boolean type, near as I can tell. Sorry, I thought you were talking about the SQL-92 spec's boolean type. I'm not even sure why they called this mode boolean, it seems case-sensitivity makes them think boolean...

Sharif

Yea that whole case-sensitive thing sucks with MySQL!