PHP/MySQL Dual FULLTEXT index search?

Am I out of luck if I want to search across tables?

[androidtech]

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.

[jatar_k]

<shameless mod bump> ;)