Fast store -- unless adding to cart or checkout_process

[tomarriola]

When viewing products at my store the experience is very fast. I'm using the latest zencart barely modified. I have a server with 2 gigs of ram, RAID and a dual processor.

The problem is... 2 actions take 10 seconds or more to complete

-add an item to your cart
-The final page of the checkout. This takes 15 seconds to process even if its a money order.

To me it appears that writing to my database is a bottleneck. My store has
about about 14,000 orders and the same amount of customers.

Any suggestions?

The store is at
http://www.crimescene.com/store

Thanks

Tom

[Ajeh]

I'd backup your database, do a repair, and an optimize on it then check that your indexes are correct ...

[tomarriola]

Thanks for the fast response

I backed up the database and used phpmyadmin to do a repair and optimize

But, how do I check to see that the indexes are correct? What am I looking for?

Thanks

Tom

[DrByte]

What version of Zen Cart?
What addons/contributions do you have installed?
What version of PHP+MySQL?

[tomarriola]

Its zencart 1.3.7 and the modifications are below. The store is running on MySQL 4.1.12 and PHP 4.4.0 via linux

rss_feed_v2.0.727.10.20071752
Google Base Feeder v1.5.3
sales_report_rev104
Fedex Shipping Module v1.3.6
currency_update.php
email_address_exporter_1.2c

This problem existed before I installed these modifications.

Thanks
Tom

[tomarriola]

This info on the database patch history may be useful:

Zen Cart 1.3.7.1
Database Patch Level: 1.3.7
v1.3.6 [2007-10-21 18:57:11] (Version Update 1.3.5->1.3.6)
v1.3.5 [2007-10-21 18:57:11] (Version Update 1.3.0.2->1.3.5)
v1.3.0.2 [2007-10-21 18:57:09] (Version Update 1.3.0.1->1.3.0.2)
v1.3.0.1 [2007-10-21 18:57:09] (Version Update 1.3.0->1.3.0.1)
v1.3.0 [2007-10-21 18:57:09] (Version Update 1.2.7->1.3.0)
v1.2.7 [2007-10-21 18:57:07] (Version Update 1.2.6->1.2.7)
v1.2.6 [2007-10-21 18:56:46] (Version Update 1.2.5->1.2.6)
v1.2.5 [2005-07-24 11:53:01] (Fresh Installation)

Tom

[DrByte]

how do I check to see that the indexes are correct? What am I looking for?

Compare your database table index details (ie: via phpMyAdmin) with the index definitions on each table in the original zc_install/sql/mysql_zencart.sql structure.
Yup ... fun ...

[DrByte]

This problem existed before I installed these modifications.

You recently did a massive upgrade. Did this problem exist before the upgrade too?

[tomarriola]

Checkout had been getting slower and slower--before the upgrade. The shopping cart slowness is new.

I'm unclear about what to look for when comparing indexes from my current database to a fresh install.

I used phpmyadmin to list indexes from both. They are attached.

Perhaps someone can give me a clue about what to do.

Thanks
Tom

[DrByte]

It does seem like you have a lot of duplication. The index names starting with "idx_" and ending with "_zen" are generally the ones Zen Cart created. Other indexes may be a result of old database structures and/or customization and/or mods/addons.

Zen Cart did do some index renaming between 1.2.x and 1.3.x; however, it should not have left-behind the old index names.

What to do? Carefully review each table's indexes.
1. Delete any indexes matching BOTH these criteria:
---a) whose names don't start with idx_ and end with _zen
---b) AND have identical "field" details as one of the idx_xxxxxx_zen indexes

This *may* leave some other indexes that don't start with idx_ and end with _zen. That's fine. The first goal is to remove duplication, since over-indexing can cause some slowdowns.

2. If there are any indexes "missing" from your current database but which exist in a fresh install, create those indexes on your current database.