How do I optimize the folllowing query?

[securegds]

hi,

I have implemented the following query to search the db for exact phrase (ex : if the search term is "air" should match the exact word not like hair).

Quote:

select A.*, B.*,C.GGCategoryID,C.CategoryName,C.CategorySlugName,XX.EcoStatus from tbl_products A,tbl_members B,tbl_product_categories C ,tbl_eco_ratings XX WHERE A.MerchantMemberID=B.MemberID and B.MemberType='Merchant' and A.GGCategoryID=C.GGCategoryID and XX.EcoStatusValue=A.EcoStatusValue and A.Status='Active' and C.Status='Active' and C.isDeleted='No' and ( (A.ProductName REGEXP '[[:<:]]#SEARCHTERM#[[:>:]]' OR A.ProductName REGEXP '[[:<:]]#SEARCHTERM#s[[:>:]]' ) OR (A.ProductDescription REGEXP '[[:<:]]#SEARCHTERM#[[:>:]]' OR A.ProductDescription REGEXP '[[:<:]]#SEARCHTERM#s[[:>:]]' ) OR (B.MemberNickName REGEXP '[[:<:]]#SEARCHTERM#[[:>:]]' OR B.MemberNickName REGEXP '[[:<:]]#SEARCHTERM#s[[:>:]]' ) OR (B.ScreenName REGEXP '[[:<:]]#SEARCHTERM#[[:>:]]' OR B.ScreenName REGEXP '[[:<:]]#SEARCHTERM#s[[:>:]]' ) OR (A.ProductType REGEXP '[[:<:]]#SEARCHTERM#[[:>:]]' OR A.ProductType REGEXP '[[:<:]]#SEARCHTERM#s[[:>:]]' ) OR (C.CategoryName REGEXP '[[:<:]]#SEARCHTERM#[[:>:]]' OR C.CategoryName REGEXP '[[:<:]]#SEARCHTERM#s[[:>:]]' ) ) and A.isDeleted = 'No' and B.MerchantType = 'Online' Group By A.`ProductName` Order By ProductID DESC LIMIT 0,10

some info:

i) table A has more than 100,000 records
ii)I didn't use LIKE (%blahblah%) and MATCH AGAINST
ii)Major columns are indexed in each table

But I feel that , it has some disadvantages over here when using this above query (MAY BE it is REGEXP).

I am not expert in MYSql,So you guys explain the disadvantages for the above query, what are the pitfalls , performance issues etc..

Also It would be helpful if you guys tell me the alternate way of this above query..

Regards!
securegds

[mwasif]

Why don't you use FULLTEXT ?

[securegds]

Hi,

I have used the full text search and also modified the query though it gives a bad performance...

Please help me out of this...

EXPLAIN SELECT A . * , B . * , C.GGCategoryID, C.CategoryName, C.CategorySlugName, XX.EcoStatus
FROM tbl_products A, tbl_members B, tbl_product_categories C, tbl_eco_ratings XX
WHERE A.MerchantMemberID = B.MemberID
AND B.MemberType = 'Merchant'
AND A.GGCategoryID = C.GGCategoryID
AND XX.EcoStatusValue = A.EcoStatusValue
AND A.Status = 'Active'
AND C.Status = 'Active'
AND C.isDeleted = 'No'
AND (
(

MATCH (
A.ProductName
)
AGAINST (
'+samsung'
IN BOOLEAN
MODE
)
)
OR (

MATCH (
A.ProductDescription
)
AGAINST (
'+samsung'
IN BOOLEAN
MODE
)
)
OR (

MATCH (
B.MemberNickName
)
AGAINST (
'+samsung'
IN BOOLEAN
MODE
)
)
OR (

MATCH (
B.ScreenName
)
AGAINST (
'+samsung'
IN BOOLEAN
MODE
)
)
OR (

MATCH (
A.ProductType
)
AGAINST (
'+samsung'
IN BOOLEAN
MODE
)
)
OR (

MATCH (
C.CategoryName
)
AGAINST (
'+samsung'
IN BOOLEAN
MODE
)
)
)
AND A.isDeleted = 'No'
AND B.MerchantType = 'Online'
GROUP BY A.`ProductName`
ORDER BY ProductID DESC
LIMIT 0 , 10

Note:
the keyword is "samsung"

is the query optimized? if not , please explain me..