List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:May 1 2001 1:17pm
Subject:Re: MATCH AGAINST 2-3 tables
View as plain text  
You need to add the table joining information the the where clause so 
the join only returns properly joined records.

EX: where ta.id=tb.id

Paul Van Slyke wrote:

> 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 <mysql-thread72739@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Gerald L. Clark
gerald_clark@stripped

Thread
MATCH AGAINST 2-3 tablesPaul Van Slyke30 Apr
Re: MATCH AGAINST 2-3 tablesPaul Van Slyke30 Apr
  • Re: MATCH AGAINST 2-3 tablesGerald Clark1 May