Slow Query

[jlaw]

I have the following query:

select count(listingmerchandise.ListingID) as Count, CategoryDescription from categoryleaf, listingmerchandise where categoryleaf.LeafID =listingmerchandise.CategoryID and listingmerchandise.ListingDescription matches '%"gold"%' and listingmerchandise.BreadCrumbID matches '%~~281~~%' and categoryleaf.ParentID = 281 and categoryleaf.CategoryID <> 281 group by CategoryDescription order by CategoryDescription

The problem is the query takes too long. I have a compound metamorph index on listingmerchandise BreadCrumbID and ListingDescription. Any ideas on how to optimize this query.

[John]

You should be using LIKE instead of MATCHES in your query. The Metamorph syntax is not the same as the standard SQL LIKE. You should probably have a metamorph inverted index on listingmerchandise(ListingDescription\BreadCrumbID), and do the search as:

ListingDescription\BreadCrumbID like 'gold ~~281~~'

You will need to modify the index expressions to account for the format of your BreadCrumbIDs.