View by manufacturers_id is so slow, why?

If you visit my website and for example view by manufacturer, these are the stats

Parse Time: 37.432 - Number of Queries: 62057 - Query Time: 12.9304016413

However, if you use the search box and search for that manufacturer, you get this

Parse Time: 3.168 - Number of Queries: 1003 - Query Time: 1.95451326323

Same number of products shown on results page.

Is there anyway to speed up the listing by manufacturer?

One of the team who make zen cart must be able to answer this one.

No clue ... have you an URL to your site that we could perhaps peek at? :unsure:

Yep, here you go remove the spaces...

f t v p .co.uk/index.php?main_page=index&manufacturers_id=2

and then searching for the same thing

f t v p .co.uk/index.php?main_page=advanced_search_result&search_in_description=1&keyword=samsung

Just as a test, turn off the New Products on the Index Listing ...

One reason for the slowness is the Category Dropdown of the 1000 Categories that have to be found among the 1000s of Categories that you have ...

Make sure to check also that your indexes on the database tables are complete ...

You could either check the:
/zc_install/sql/mysql_zencart.sql

Or you could install a test directory with a test database so you can see the tables in a database with all of the indexes that should exist ...

You can also look at the SQL statements in the:
/includes/index_filters/default_filter.php

to see what all is in the WHERE statements to see how they are written and the affect your tables would have, especially if missing indexes on your database tables ...

Could you go to phpMyAdmin and check some things about this
products_id 10901
Product: Samsung PS-50C450B1W - PSU - BN44-00414A Rev 1.1 - J40 - HU10251-10010
TV Model Number: PS-50C450B1W

Look in the table:
products

and search for the products_id 10901

What is the master_categories_id on this product?

Search for the master_categories_id as the categories_id on the products_id 10901 in the tables:
categories
categories_description

Do you find it? what is the parent_id in the table categories for this categories_id that matches the master_categories_id

Look in the table:
products_to_categories

and search for the products_id 10901

What categories_id come up for this products_id?

Save those categories_id to NotePad ...

Look in the table:
categories
categories_description

and search for those categories_id and see what their parent_id values are ...

Browse your table:
products

how many of the master_categories_id are 0 ... you can do a search for master_categories_id is 0 to get a count ...

Browser your table:
products_to_categories

search on the categories_id for 0 ... how many come up?

What version of Zen Cart are you running?

Clean install or an Upgrade?

Do you use Linked Products?

Are you Importing your Products?

Turning off new product listing makes no change to speed.

What is the master_categories_id on this product? = 5194

categories = parent_id = 3059

products_to_categories = categories_id = 5194

master_categories_id are 0 = 4 items

products_to_categories -> categories_id 0 = 0 items

v1.3.9h
Clean Install
No linked products
Not importing products

Find the 4 products_id that have the 0 on the master_categories_id and set them to their proper master_categories_id ... you can do this by going to the Catalog ... Products ... and find the Products then hit the C for copy and the Multiple Links Manager and set a master_categories_id for them ...

For giggles, switch to the Classic Template and see what the speed is on the Manufacturer look up ...

I am trying to see why your centerboxes are not using the cPath in their URLs ...

I removed the cPath from the links.

I did this to tidy up the url for social network integration.

Rebuilt the four with missing master categories.

Parse Time: 33.973 - Number of Queries: 62139 - Query Time: 11.9520936854

As a test, have you tried the Classic Template just to see if there is a difference?

As another test, go to the Configuration ... Product Listing ... and turn this off for a minute and see how the Manufacture list works:

Display Category/Manufacturer Filter (0=off; 1=on)
Do you want to display the Category/Manufacturer Filter?

Turned off category/manufacturer filter

Parse Time: 34.661 - Number of Queries: 62124 - Query Time: 12.2081060008

Default template

Parse Time: 1.475 - Number of Queries: 1426 - Query Time: 0.748605966461

INTERESTING.......

What on earth in that template could be causing that then...

My template only causes the view by manufacturer to be slow, and not searches.

This is narrowing it down ...

At least now it is something in one of your template files causing this issue ...

Are you able to switch it to Classic Template for 5 minutes to let me try a couple things?

I don't know, the problem is this is a live site that pays for food.

Does it run like a dog on the sister site? :unsure:

If so, I can try to see if I can spot something there ...

It doesn't seem to, but there are alot fewer products

It would really need to be where the problem is ...

What I would do if I were you is use something like Beyond Compare and see what files are different for your templates and overrides and the original files ...

You may be able to spot which files are changed and see what files are changed in a manner that would be slowing things down ...

You might try also turning off the right column for a second to see if that page loads any faster just to rule out something in the right column being the problem ...

Then, you might try turning off the footer for a second to see if that page loads any faster just to rule out something in the footer being the problem ...

Then, you might try turning off the header for a second to see if that page loads any faster just to rule out something in the header being the problem ...

It is strange that only view by manufactuers_id is slow.

I will try your suggestions and report back.

May take some time as I've made so many customisations.

I upgraded the server, now I get this result

New : Parse Time: 13.933 - Number of Queries: 63224 - Query Time: 2.89328504413
Old : Parse Time: 37.432 - Number of Queries: 62057 - Query Time: 12.9304016413

My previous dedicated server

2 x dual core 3.0ghz xeon
2gb ram
7200pm sata drives

new server

2 x 6core amd 3.1ghz
32gb ddr3 1800mhz ram
15k SAS drives

big improvement