MySQL Indexing

[GJay]

Hope someone can help, not had much experience with indexing tables for optimality, but the product-search on a site I've been involved with is painfully slow, and I want to speed it up. It's based on OSCommerce, which is woefully inefficient, but there's nothing to be done about that, so will have to struggle with the database. The particulatly noticeable slowness is (ironically enough...) with the 'quick search' functionality.
The query is essentially:
SELECT p.image, pd.name, pd.short_description, p.price FROM products p JOIN products_description pd ON (p.id=pd.productid) WHERE pd.lang=1 AND pd.name LIKE '%keyword%' OR pd.description LIKE '%keyword%' OR p.model LIKE '%keyword%';

It's annoying that the system uses two tables when (in this case at least, with only one language) one would suffice.

When in the context of the page, a search using the query as above takes about 15-20 seconds to load (this is running on a more than adequate server, on a local network), running just the query from the MySQL console registers about 7 seconds.

What should I be indexing? If it were ANDs joining the WHERE-conditions then it'd be simple, but is there anything that can be done?
The OSC paging-system does a SELECT COUNT(*) FROM... using the same query as above, and this is rather slow as well, so any pointers in that direction would be appreciated.

I'm more familiar with postgres, and so don't have much of a clue about FULLTEXT indexing? Would this be of any benefit?

The databases contains around 50k products and accompanying descriptions so while not tiny, is not exactly huge and shouldn't be this slow.

I've not got a lot of time to spend on this, so while re-designing things would be the best solution, it simply isn't possible, so any fixes/pointers, no matter how inelegant are appreciated

[raf]

it's always hard to suggest on performance issues, without knowing the db-design/sitedesign/datapropertys.

what i find strange in your query is the " JOIN " --> shouldn't this be " INNER JOIN "

anyway;
- colums that should be indexed: p.id and pd.productid --> should be numeric datatypes, and as small as possible.
- pd.lang=1 should be removed from your where-clause(since it'll evaluate to True for all records).
- creating FULLTEXT indexes wount help you. Keywordsearches and fulltext searches are two completely different things (search this forum for explanation)
- if possible: clean up the db. Maybe not all 50k products should be inthere anymore...
- your search could maybe be optimized by not allways searching on the 3 fields (maybe 3 searches? or have a fourth field where you concatinate the 3 other fields, and then use that as the only field to search on) (--> redundant data but will speed up the searches)
- use minimal lengths for the keywords and remove frequently used words (depends on your product, but on a site about music for instance, you best remove words like 'artist', 'band', etc from searchinput.
- if the productdata isn't changing often, you could create a seperate table (with all collumns you need from products and products_description + maybe the concatinated fourth field) where you run the search on (--> again redundant data, but will speed up the searches and might have a possitve effect on other db-processes)
- maybe improve the site-structure --> people ussualy only search when the data is not structured properly.