problem with fulltext search query - help

ppowell

select id, image_name, match (image_name, image_alt, image_location_city, image_location_state, image_location_country) against ('california') as score from image where match (image_name, image_alt, image_location_city, image_location_state, image_location_country) against ('california')

This simple fulltext query will return results based on the keyword "california". I happen to have a table with only 25 rows right now, observe these results:

mysql> select count() from image;
+----------+
| count() |
+----------+
| 25 |
+----------+
1 row in set (0.00 sec)

mysql> select id from image where image_name like '%green%' or image_alt like '%green%' or image_location_city like '%green%' or image_location_state like '%green%' or image_location_country like '%green%';
+-----+
| id |
+-----+
| 40 |
| 148 |
| 166 |
| 167 |
| 71 |
| 164 |
| 150 |
| 165 |
| 168 |
| 169 |
| 170 |
| 171 |
| 172 |
| 173 |
| 175 |
| 178 |
| 187 |
+-----+
17 rows in set (0.00 sec)

mysql> select id from image where match (image_name, image_alt, image_location_city, image_location_state, image_location_country) against ('green' IN BOOLEAN MODE);
+-----+
| id |
+-----+
| 40 |
| 148 |
| 166 |
| 167 |
| 71 |
| 164 |
| 150 |
| 165 |
| 168 |
| 169 |
| 170 |
| 171 |
| 172 |
| 173 |
| 175 |
| 178 |
| 187 |
+-----+
17 rows in set (0.00 sec)

mysql> select id from image where match (image_name, image_alt, image_location_city, image_location_state, image_location_country) against ('green'); Empty set (0.00 sec)

mysql>

If I take off "IN BOOLEAN MODE" the query fails in MySQL 4.0.10 (and putting it in is not an option in MySQL 3.23.58). Could someone show me what, just based on this query, I have done wrong?

Here is more info that might help:

mysql> show index from image;
+-------+------------+---------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| image | 0 | PRIMARY | 1 | id | A | 25 | NULL | NULL | | BTREE | |
| image | 1 | ix_image_path | 1 | image_path | A | NULL | NULL | NULL | | BTREE | |
| image | 1 | ix_search | 1 | image_name | A | NULL | NULL | NULL | | FULLTEXT | |
| image | 1 | ix_search | 2 | image_alt | A | NULL | NULL | NULL | YES | FULLTEXT | |
| image | 1 | ix_search | 3 | image_location_city | A | NULL | NULL | NULL | YES | FULLTEXT | |
| image | 1 | ix_search | 4 | image_location_state | A | NULL | NULL | NULL | YES | FULLTEXT | |
| image | 1 | ix_search | 5 | image_location_country | A | NULL | NULL | NULL | YES | FULLTEXT | |
+-------+------------+---------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)

Thanx
Phil

ppowell

I created a dummy table this time in MySQL 3.23.58 and here are the results, I'm stuck:

mysql> select * from blah;
+----+------------+-----------+-----------+-------+-------+
| id | first_name | last_name | city | state | zip |
+----+------------+-----------+-----------+-------+-------+
| 1 | Phil | Powell | Fairfax | VA | 22033 |
| 2 | Phillip | Powell | Reston | VA | 20190 |
| 3 | Eduardo | Takamura | Greenbelt | MD | 20720 |
| 4 | Mary | Shugrue | Greenbelt | MD | 20720 |
| 5 | Michael | Bryzak | London | | |
| 6 | Mary | Smith | Ann Arbor | MI | 48103 |
| 7 | Ann | Smith | New York | NY | 10023 |
| 8 | Ann | Richards | Austin | TX | 78701 |
+----+------------+-----------+-----------+-------+-------+
8 rows in set (0.01 sec)

mysql> select id, match (first_name, last_name, city, state, zip) against ('Ann') as score from blah where match (first_name, last_name, city, state, zip) against ('Ann');
Empty set (0.00 sec)

mysql> select id, match (first_name, last_name, city, state, zip) against ('Ann') as score from blah where match (first_name, last_name, city, state, zip) against ('Ann');
Empty set (0.00 sec)

mysql> show index from blah;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----------+
| blah | 0 | PRIMARY | 1 | id | A | 8 | NULL | NULL | |
| blah | 1 | ix_blah | 1 | first_name | A | NULL | NULL | NULL | FULLTEXT |
| blah | 1 | ix_blah | 2 | last_name | A | NULL | NULL | NULL | FULLTEXT |
| blah | 1 | ix_blah | 3 | city | A | NULL | NULL | NULL | FULLTEXT |
| blah | 1 | ix_blah | 4 | state | A | NULL | NULL | NULL | FULLTEXT |
| blah | 1 | ix_blah | 5 | zip | A | NULL | NULL | NULL | FULLTEXT |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----------+
6 rows in set (0.00 sec)

I am learning more about fulltext searches at sites such as http://www.samspublishing.com/articles/article.asp?p=30875&seqNum=9 and yet I still cannot master this and I am having to build a search engine using MySQL. HELP!

Thanx
Phil

tsinka

Hi,

on MySQL 3.x MATCH won't match any words with less than 4 characters even when using *.

As of MySQL 4.0.0 you can use ft_min_word_len in the [mysqld] section in php.ini to set the minimum length. You should add that to the myisamchk section, too, if you use myisamchk.

[mysqld]
ft_min_word_len=3

Thomas

ppowell

you lost me completely. What is myisamchk?

And I cannot alter php.ini as I am unable to do so. Would I be allowed to do this via ini_set()?

Phil

tsinka

Sorry,

my fault ... a typo ... I meant my.cnf, the MySQL configuration file.

I think that there is no other way to solve that problem than modifying my.cnf.

myisamchk is a tool to check MySQL tables if you encounter any problems.

Thomas

ppowell

That's just it, I cannot alter any of the .conf files as I am forbidden to do so due to permissions restrictions on the server.

Phil

tsinka

I think, that limit has be chosen to prevent performance problems. If you set that value to a very low value you can get a serious performance hit.

Another important note:

If the string you try to match against are found in more than 50% of all rows the result will be an empty result set because MySQL interprets the string as stop word in that cases. There are several sections in the manual dealing with the limitations of fulltext searches.

Thomas

tsinka

Hi,

a really really really really ugly workaround would be to prefix all the column values in the table with e.g. ---- to make sure that they exceed the length limit.

But you would have to add this prefix to all your queries if you use e.g. column='value'. And you would have to remove the prefix when fetching colums.

However, this would have an impact on the score on fulltext search queries.

Thomas

ppowell

Ok, let me ask you this then:

Do you think this is a good approach to the problem:

1) Generate HUGE query of LEFT JOINS on multiple tables and get a resultset
2) Create temporary table of the resultset
3) Use "match() against()" query on the temporary table to get your results

I know that in most cases I will get nothing back because either there will be too few rows returned, or the row return resultset will have 50% or more of the entire rowset of the temporary table, or the search keyword will be too small, or...

But the problem I'm having with this is a simple one: the client wants to SORT the resulting resultset on the fly. I don't know of a clean way to do this other than to repeat Steps 1 -3 over and over and over again.

Phil