471,620 Members | 1,383 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,620 software developers and data experts.

Searching across tables, each with a FULLTEXT index?

I read a while back that MySQL will only use one index per query. (If this
is not so, please tell me and point me to a doc that gives a good
explanation of MySQL's current index usage policy). I'm using MySQL 4.2.x.

Here's my dilemma.

1) ---------

I have two tables that have records with a FULLTEXT index text field in each
of them. The problem is the relationship between the tables is a
one-to-many relationship. I'll make some dummy info to make it easier to
describe the problem:

Table 1: "tableOne"

field: "user_info", text - FULLTEXT indexed

Table 2: "tableMany"

field: "user_comment", text - FULLTEXT indexed

There are many "tableMany.user_comment" records per "tableOne.user_info"
record. So if I want to offer a FULLTEXT search of all the across all the
"user_info" records and all the "user_comment" records, my guess is that
MySQL will reduce it to a fast FULLTEXT search of one of the tables, and
then sequentially search the second? Is that correct?

If so, with the size of my database and the number of requests per day my
server handles, I could not reasonably support that search on my server.

Any thoughts or creative ideas here?

2) -------------------

Just to clarify my knowledge:

If I have a query like:

SELECT * FROM dummy_table WHERE MATCH(some_text_field) AGAINST ("+some
words" IN BOOLEAN MODE) AND user_id=9898989;

Then even if "some_text_field" is FULLTEXT indexed AND user_id is regular
indexed, only one of these indices will be used during the query, is that
correct?

3) -----------------

Also, if an SQL query accesses multiple tables, like in a JOIN, does the one
index per query rule still apply? Or is it one index per table per query (I
hope)?

Thanks.
Aug 25 '05 #1
1 2271
Hi Robert,

I have built a searchengine that indexes the words from multiple tables.
You can put a weight on each column whose values you want it to index.
The weight are used to calcualte a score to sort the result. This would
for example allow you to index newsgroup messages and to give the
subject line 10 times more weight then the message body. In other words,
to get the same ranging one word in the subject counts for 10
occurrences of the same word in the body. To try it out see
http://semantha.metaclass.nl/museum/...=WebSearchPage
As you can see there are website pages, collecters items, books and
cdroms in a single resultset. These come from three different tables
with totally different layout, and some with many fields.

An interesting option is to present the words found in the index so that
the user can select/unselect the words that are approriate. This is
especially nice for languages like german and dutch, where concatenated
words tend to mess up search results. To try this out, select the
checkbox "toon woorden" in the page begind the previous hyperlink. In
this mode you can also add new words by searching for them without
losing the ones you previously selected.

Greetings,

Henk Verhoeven,
www.metaclass.nl, www.phppeanuts.org.

(The search engine is not open source, currently it is only sold as part
of other products)

Robert Oschler wrote:
I read a while back that MySQL will only use one index per query. (If this
is not so, please tell me and point me to a doc that gives a good
explanation of MySQL's current index usage policy). I'm using MySQL 4.2.x.

Here's my dilemma.

1) ---------

I have two tables that have records with a FULLTEXT index text field in each
of them. The problem is the relationship between the tables is a
one-to-many relationship. I'll make some dummy info to make it easier to
describe the problem:

Table 1: "tableOne"

field: "user_info", text - FULLTEXT indexed

Table 2: "tableMany"

field: "user_comment", text - FULLTEXT indexed

There are many "tableMany.user_comment" records per "tableOne.user_info"
record. So if I want to offer a FULLTEXT search of all the across all the
"user_info" records and all the "user_comment" records, my guess is that
MySQL will reduce it to a fast FULLTEXT search of one of the tables, and
then sequentially search the second? Is that correct?

If so, with the size of my database and the number of requests per day my
server handles, I could not reasonably support that search on my server.

Any thoughts or creative ideas here?

2) -------------------

Just to clarify my knowledge:

If I have a query like:

SELECT * FROM dummy_table WHERE MATCH(some_text_field) AGAINST ("+some
words" IN BOOLEAN MODE) AND user_id=9898989;

Then even if "some_text_field" is FULLTEXT indexed AND user_id is regular
indexed, only one of these indices will be used during the query, is that
correct?

3) -----------------

Also, if an SQL query accesses multiple tables, like in a JOIN, does the one
index per query rule still apply? Or is it one index per table per query (I
hope)?

Thanks.

Aug 25 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Ralph Guzman | last post: by
reply views Thread by michael calwell | last post: by
4 posts views Thread by Michi | last post: by
3 posts views Thread by Ondernemer | last post: by
reply views Thread by Alex Glass | last post: by
2 posts views Thread by chernetsov | last post: by
1 post views Thread by saavedrajj | last post: by
1 post views Thread by ZEDKYRIE | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.