This is posted in another forum as well but towards the end of a thread and urgently needs to be done

i am trying to speed up searches and the one search is a unique number. i have removed all % wildcards and looked at the rows that are searched and the times in slow query logging and here is what im getting.

with wildcards on:

Time: 061226 8:26:52

User@Host: aepinc_zc1[aepinc_zc1] @ localhost []

Query_time: 12 Lock_time: 0 Rows_sent: 1 Rows_examined: 2319868

SELECT DISTINCT p.products_image, p.products_quantity , m.manufacturers_id, p.p
roducts_id, pd.products_name, p.products_price, p.products_tax_class_id, p.produ
cts_price_sorter FROM zen_products p

LEFT JOIN zen_manufacturers m

USING(manufacturers_id), zen_products_description pd, zen_categorie
s c, zen_products_to_categories p2c

LEFT JOIN zen_meta_tags_products_description mtpd

ON mtpd.products_id= p2c.products_id

AND mtpd.language_id = 1 WHERE p.products_status = 1

AND p.products_id = pd.products_id

AND pd.language_id = 1

AND p.products_id = p2c.products_id

AND p2c.categories_id = c.categories_id AND ((p.products_model L
IKE '0060891246' OR (mtpd.metatags_keywords

LIKE '%0060891246%'

AND mtpd.metatags_keywords !='')) ) order by p.products_
sort_order, pd.products_name limit 0, 20;

with wildcards off:

Time: 061226 10:14:12

User@Host: aepinc_zc1[aepinc_zc1] @ localhost []

Query_time: 12 Lock_time: 0 Rows_sent: 1 Rows_examined: 2319868

SELECT DISTINCT p.products_image, p.products_quantity , m.manufacturers_id, p.p
roducts_id, pd.products_name, p.products_price, p.products_tax_class_id, p.produ
cts_price_sorter FROM zen_products p

LEFT JOIN zen_manufacturers m

USING(manufacturers_id), zen_products_description pd, zen_categorie
s c, zen_products_to_categories p2c

LEFT JOIN zen_meta_tags_products_description mtpd

ON mtpd.products_id= p2c.products_id

AND mtpd.language_id = 1 WHERE p.products_status = 1

AND p.products_id = pd.products_id

AND pd.language_id = 1

AND p.products_id = p2c.products_id

AND p2c.categories_id = c.categories_id AND ((p.products_model L
IKE '0060010169' OR (mtpd.metatags_keywords

LIKE '0060010169'

AND mtpd.metatags_keywords !='')) ) order by p.products_
sort_order, pd.products_name limit 0, 20;

I have tried playing w/ the querie as much as possable using LIMIT 1 but cant really figure how to get that to work without breaking the search

i also tried with what i created as "advanced_search_result1" and even if i can get "LIMIT 50" to work without breaking the search i still get 3000+ search results on certain keywords. which is a 24 second query.

so far i have made sure i have indexes on tables that are needed ,
tweaked my my.cnf file, installed php accelerator, and we have upgraded or server to run
multi dual core woodcrest proccessors
2 gb ram
2 sas hdd's

running mysql from second drive

i think at this point the only place i am gonna find more speed is within the way the queries work themselves and finding a way to stop the queries once the limit has been found.

i have scoured both the zend and oscommerce forums and havent come up with much too much on this.

to view the site http://aepbooks.com/shop1/

Thanks
Mike Z

still looking for some help w/ this, have gotten db pretty tweaked and have moved "products_name" field to products table, and has made a big differance, but still need to limit the searches to 100 or so items. people arent going to go through 6000 results to buy a book, and there sure not gonna wait for the search to finish before moving on!!!! any help would be appreciated in limiting the query !!!!!