Thanks for the response!
The two tables contain different types of information which are related
by a foreign key (orginally designed that way). However, there is no
reason that there can't be a single table that contains both (actually 3
seperate tables) sets of data. Unfortunately, building the
FULLTEXT index on the third table is incredibly slow on tables sizes
500Megs or more.
So, it sounds like we don't have much of a choice. Your suggestion to
have one table for the FULLTEXT searches seems the best way to go.
What we decided to do is to compile mysql-4.0, build a combined table of
the three (which will be over a GIG) and index it under 4.0. Then move
the tables over to 3.23 for production use.
Thanks! Off to compile 4.0!
Paul
>I've got two seperate tables that I would like to query using a MATCH
..
>AGAINST() syntax.
Why are they two separate tables?
>If I run the query individually on one table, the
>speed of the returned results is great! Very fast, indeed. However,
>when I try and use the following statement for querying both tables at
>the same time, it is painfully slow. Too slow to use in a production
>environment.
>SELECT DISTINCT * from tA, tB WHERE MATCH tA.aName AGAINST ('name') ||
>MATCH tB.bName AGAINST ('name');
You are doing a join between two tables. This means that if table A
has 100 matches and table B has 400 matches, you get 40,000 results
(before applying DISTINCT). *SLOW* !!!
I don't know the structure of your tables, but if they are identical,
why aren't these in one table (with an extra column for which table
they belong in) ?
>The fast queries are....
>SELECT DISTINCT * from tAWHERE MATCH tA.aName AGAINST ('name');
>SELECT DISTINCT * from tBWHERE MATCH tB.bName AGAINST ('name');
Gordon L. Burditt
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
