Slow Searching in MySQL - How to Overcome this Individual Problem

[godwheel]

Hello Fellas:

Alright, this is driving me insane. I've tried a million things to make my search faster, but to no avail -- including adding indexes which were sadly missing hehe.

Basically, I want to search through the database for anyword used in the search. For example, if someone searches for wedding dress, i want it to return anything to do with Wedding Dress. Example: Anyone in the Wedding Dress Category, with a small description that mentions the phrase or a large description that mentions the phrase. In addition, if the company name is something like "Michael's Wedding Dress Specialities."

DATABASE - I've only pasted what is relevant to the search to simplify things.
DB used is MyISAM

TABLE Customers
customer_id - PK
company_name - NORMAL INDEX
sm_desc (VARCHAR 100) - FULLLTEXT INDEX
lg_desc (TEXT) - FULLTEXT INDEX

TABLE Categories
cat_id - PK
cat_name = NORMAL INDEX

TABLE SubCategories
subcat_id - PK
subcat_name - NORMAL INDEX
cat_id - NORMAL INDEX

TABLE SubCatList
cat_id - NORMAL INDEX
customer_id - NORMAL INDEX
subcat_id - NORMAL INDEX

MY search looks something like this:

SELECT Distinct
SubCatList.customer_id
FROM
SubCatList, Categories
WHERE
(
(Categories.cat_id = SubCatList.cat_id) AND
(Categories.cat_name LIKE \"%".$_POST['search_me'] ."%\")
)

UNION

SELECT Distinct
SubCatList.customer_id
FROM
SubCatList, SubCategories
WHERE
(
(SubCategories.subcat_id = SubCatList.subcat_id) AND
(SubCategories.subcat_name LIKE \"%".$_POST['search_me'] ."%\")
)

UNION

SELECT Distinct
SubCatList.customer_id
FROM
SubCatList,Customers
WHERE
(
(SubCatList.customer_id = Customers.customer_id) AND

(MATCH (Customers.sm_desc) AGAINST ('".$_POST['search_me']."'))
)

UNION

SELECT Distinct
SubCatList.customer_id
FROM
SubCatList, Customers
WHERE
(
(SubCatList.customer_id = Customers.customer_id) AND

(MATCH (Customers.lg_desc) AGAINST ('".$_POST['search_me']."'))
)

UNION

SELECT Distinct
SubCatList.customer_id
FROM
SubCatList,Customers
WHERE
(
(SubCatList.customer_id = Customers.customer_id) AND

(Customers.company_name LIKE \"%".$_POST['search_me'] ."%\" )
)

Needless to say, it is SLOW as molases. What do you guys recommend I can do? I'm aware that using % nullyfies the use of indexes, but then how do I do effective searches when I want to return more results?

What do you guys think I should do to make the search faster?

Thanks!

[bastien]

try merging all of these queries into one query

SELECT Distinct
`SubCatList`.`customer_id`
FROM
`SubCatList`, `Categories`, `SubCategories`, `Customers`
WHERE
(
(`Categories`.`cat_id` = `SubCatList`.`cat_id`) AND
(`Categories`.`cat_name` LIKE \"%".$_POST['search_me'] ."%\")
)
or
(
(`SubCategories`.`subcat_id` = `SubCatList`.`subcat_id`) AND
(`SubCategories`.`subcat_name` LIKE \"%".$_POST['search_me'] ."%\")
)
or
(
(`SubCatList`.`customer_id` = `Customers`.`customer_id`) AND
(MATCH (Customers.sm_desc) AGAINST ('".$_POST['search_me']."'))
)
or
(
(`SubCatList`.`customer_id` = `Customers`.`customer_id`) AND
(MATCH (Customers.lg_desc) AGAINST ('".$_POST['search_me']."'))
)
or
(
(`SubCatList`.`customer_id` = `Customers`.`customer_id`) AND
(`Customers`.`company_name` LIKE \"%".$_POST['search_me'] ."%\" )
)