FULLTEXT searching

[bwest]

I am pretty good with mysql but I haven’t done much full text searching. I have this problem though with getting results. I learned how to do the fulltext searching at the mysql.com site. I plugged their exact example into my database with phpmyadmin and everything good. Then when I make my real table, its not so good. I dont get any results back even with the simplest of queries. I added more rows then all of a sudden the same exact query that didn’t work STARTS WORKING :rolleyes: I am confused so I start deleting rows (rows that weren’t part of my query results) and all I have left are the rows that are significant to my query. And the query doesn’t work again.

I did some searching on google and I found one other person who was having a similar problem. He said the queries would only work if there were >5 rows.

Does anybody have any experience with these problems

-Brad

[vark]

could you post the code that your having problems with? That would be helpful.

vark

[bwest]

Here is the code from www.mysql.com. Notice they do have more than five records.

mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES
-> ('MySQL Tutorial','DBMS stands for DataBase ...'),
-> ('How To Use MySQL Well','After you went through a ...'),
-> ('Optimizing MySQL','In this tutorial we will show ...'),
-> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
-> ('MySQL vs. YourSQL','In the following database comparison ...'),
-> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

[Troy]

You have to have minimum three rows to get results with full text indexes. Really, you want more than that to get decent results. Thing is, MySQL tries to be smart and automatically ignores words that are in 50% or more of the rows. The idea is that if you have 10 million rows and 80% of them have the word "cow" in them, if you search for "cow", you probably don't want 8 million rows as your result set. It does this to help you narrow down to the most quality results. So if you have 1 row, you can't do any fulltext searches since 100% of the rows will match. If you have 2 rows, you can't do fulltext searches since 50% or 100% of the rows will match. Hope this explains what you were seeing.

[bwest]

I dont suppose there's anyway to change this? My client may have 30 rows max. (Articles for their clients) This may make searching frustrating when keywords dont get anything.

-B

[Troy]

If you only have 30 rows, just use normal search methods like:

$sql = "select name, age, sex from people where name like '%".$searchvar."%'";

You don't need fulltext indexes to search, but they serve a special purpose when searching large text block columns in large tables. I've written a lot of database apps, and I've never used fulltext indexes. I probably should have a few times, but just did need to in order to create functionality I wanted.