Hi!
On Oct 25, Lowell Allen wrote:
> Thanks for the reply, Sergei. I think I've since found the problem in the
> MySQL documentation -- under "Fulltext restrictions", 6.8.1, "All parameters
> to the MATCH function must be columns from the same table that is part of
> the same fulltext index." I need to search against two indexes from two
> tables.
>
> I can successfully do a search on both tables if I don't use fulltext, like
> this:
>
> $psl->set_query("SELECT DISTINCT Products.ID, " .
> "Products.Name, Products.Photo, Products.Description " .
> "FROM Products, Categories, ProductsCategories, Items " .
> "WHERE (Categories.ParentID=$MainCatID " .
> "AND Categories.ID=ProductsCategories.CategoriesID " .
> "AND ProductsCategories.ProductsID=Products.ID " .
> "AND (Products.Name LIKE '%$searchtext%' " .
> "OR Products.Description LIKE '%$searchtext%')) " .
> "OR (Categories.ParentID=$MainCatID " .
> "AND Categories.ID=ProductsCategories.CategoriesID " .
> "AND ProductsCategories.ProductsID=Items.ProductsID " .
> "AND Items.ProductsID=Products.ID " .
> "AND (Items.ItemNumber LIKE '%$searchtext%' " .
> "OR Items.Description LIKE '%$searchtext%')) " .
> "ORDER BY Products.Name");
>
> But this is very slow.
>
> I'm currently trying to figure out the PHP to create a temporary table to
> store the two different query selections.
>
> >> "AND MATCH (Products.Name, Products.Description) " .
> >> "AND MATCH (Items.Description) " .
> >> "AND MATCH (Products.Name, Products.Description, Items.Description) " .
Why don't you create one FULLTEXT index over all three columns ?
It even would be smaller than two your indexes :-)
Or search it like this
MATCH (col1,clo2) AGAINST ("foobar) OR MATCH (col2) AGAINST ("foobar")
Regards,
Sergei
P.S. And reply to the list, not directly to me (at least not only to me).
--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/
---------------------------------------------------------------------
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
