MySQL Forums
Forum List  »  Full-Text Search

Truncate operator in quoted expression?
Posted by: Mike Ruskai
Date: June 26, 2005 11:12PM

I use fulltext searching to allow for fast substring searches on
certain fields in a database with about 3 million records (it's
basically a data warehousing situation). Searches are ad hoc queries,
so using fulltext indexing is my attempt to reduce the number of table
scans. I have full control over the SQL syntax used. Users simply
enter search terms.

Assume a fulltext indexed field called "location" in table "foo", which
contains street addresses. Here are some sample values that illustrate
the issues I'm going to bring up:

1) "21 SYCAMORE ROAD"
2) "22 SYCAMORE RD"
3) "10 SYCAMORE AVE"
4) "12 SYCAMORE AVENUE"

First, consider record #1. The following will show one matching
result, as expected:

select count(*) from foo where match(location) against('"21 sycamore"'
in boolean mode);

These, however, do NOT work:

select count(*) from foo where match(location) against('"21 syca"' in
boolean mode);

select count(*) from foo where match(location) against('"21 syca*"' in
boolean mode);

select count(*) from foo where match(location) against('"21 syca"*' in
boolean mode);

I would expect them to work like this, which DOES work:

select count(*) from foo where location like '21 syca%';

To quell any comments about simply using the LIKE operator, let me
provide another example.

Let's assume I want to find all matching records for Sycamore Avenue.
This would be record #3 and #4. However, one is abbreviated, and one
is not.

The LIKE operator method is this:

select count(*) from foo where location like '%sycamore ave%';

That works, but requires a table scan.

select count(*) from foo where match(location) against('"sycamore ave"'
in boolean mode);

This will match record #3 only.

select count(*) from foo where match(location) against('"sycamore
avenue"' in boolean mode);

This, of course, only matches record #4.

The following matches zero rows:

select count(*) from foo where match(location) against('"sycamore
ave*"' in boolean mode);

And this duplicates the first query of this example:

select count(*) from foo where match(location) against('"sycamore
ave"*' in boolean mode);

So, is there any syntax at all that behaves as I'm expecting?
Basically, I guess I'm asking if there's any way to get the truncation
operator to actually function inside a quoted expression.

Options: ReplyQuote


Subject
Views
Written By
Posted
Truncate operator in quoted expression?
3932
June 26, 2005 11:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.