remove model from search

[zskiman]

I know someones gonna ask why

so here go's

we offer over a million products and even though our server is a beast the database is very sluggish on searches.

i would like one searchbox that will search nothing but model # (which has been turned into isbn)

and one that searches nothing but product name and stops after the first 200 rows that match the search criteria.

we do not want to search description at all as our descriptions come from amazon feeds.

i know im asking alot but if anyone can help it would really be appreciated.

to view an example of our cart http://aepbooks.com/shop1/

thanks
Mike z

[zskiman]

I have come across this in an oscommerce contribution and hope that it may help someone who has a large database and would like to get faster searches

this is on line 587 or so

$where_str .= "(pd.products_name LIKE '%:keywords%'

OR p.products_model

LIKE '%:keywords%'

OR m.manufacturers_name

LIKE '%:keywords%'";

and i have changed to:

$where_str .= "( p.products_model LIKE '%:keywords%'";

although i still have alot of twaeking work this has done wonders on searc times of isbn numbers

[Ajeh]

Try the difference between:

$where_str .= "( p.products_model LIKE '%:keywords%'"; 

versus this:

$where_str .= "( p.products_model LIKE ':keywords%'"; 

In this case you are matching on the start of the products_model (ISBN) and not worrying about the end ...

A LIKE search is the slowest of the bunch ... tweaking it a bit here may help

Also be sure that you have an index on the products_model field ...

[zskiman]

Thamk you for your response that seems to have helped a bit

unfortunatly the shop that im working on is on a server that the dns hasnt been switched over for yet.

but the shop can be seen here

http://209.85.102.109/shop1/index.ph...index&cPath=30

and the configures php arent set for functionality unless dns resolvers are set

was able to set a second search box for title/author that searches only

product_name, now the only thing i need to do is limit the search to first 100 or

so positive matches and quit .

any suggestions would be great

Thanks again
Mike Z

[zskiman]

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.

Thanks
Mike Z