Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Indexes and Databasa Optimization


oscommercenewbie

Recommended Posts

I have just uploaded 30,000 products, product descriptions, and images and my site has slowed to a crawl. I rebooted the whole server. It helped momentarily but today still does the same thing. I am on a dedicated server, with plenty of space and ram. I have read at least a hundred posts on indexes and optimization. Can someone tell in plain ole terms what one does to "create indexes" in phpmyadmin? I see the box that says create ? indexes. What exactly am I looking for when I look for "bottlenecks" etc. I am certain it isnt hard but a little help with the actual steps would be appreciated.

 

I have done the following. What next?

 

1. Click the SQL tab and enter the following in the textbox and click enter:

 

CODE

alter table categories add index (sort_order);

alter table customers add index (customers_email_address);

alter table orders add index (customers_id);

alter table products add index (products_model), add index (products_price), add index (products_date_available), add index (manufacturers_id);

alter table products_attributes add index (products_id), add index (options_id);

alter table products_options add index (products_options_name);

 

 

2. Click the Structure tab and under the table listing click Check All and select Optimize from the drop down.

Link to comment
Share on other sites

I have recently uploaded over 30,000 products too. In my case, I have around 400 categories and subcategories which ground every page to a near halt. I found that almost 1,000 queries were being run to display the category list. The solution is to optimise the code that displays the category box.

 

This file is: 'includes/boxes/categories.php'

 

Find the line in the tep_show_category function that calls tep_count_products_in_category. Comment out this section. This will remove the count of products in each category but will remove hundreds of wasted queries. When I did this my site went from 15 seconds to load a page to less than one second. I also commented out the call to tep_has_subcategories and forced all level 0 categories to be displayed with a '->'. This may or may not be applicable to your case.

 

 

Let me know if this helps,

Scott

Link to comment
Share on other sites

Will give it a try and let you know how it goes! Thank you.

 

 

I have recently uploaded over 30,000 products too.  In my case, I have around 400 categories and subcategories which ground every page to a near halt. I found that almost 1,000 queries were being run to display the category list.  The solution is to optimise the code that displays the category box.

 

This file is: 'includes/boxes/categories.php'

 

Find the line in the tep_show_category function that calls tep_count_products_in_category.  Comment out this section.  This will remove the count of products in each category but will remove hundreds of wasted queries.  When I did this my site went from 15 seconds to load a page to less than one second.  I also commented out the call to tep_has_subcategories and forced all level 0 categories to be displayed with a '->'.  This may or may not be applicable to your case.

Let me know if this helps,

Scott

Link to comment
Share on other sites

This function can be disabled in admin, without the need to hack the code.

 

admin/configuration/My Store/Show Category Counts

There are 10 types of people in the world. Those who understand binary and those who do not.

 

To understand Recursion, first one must understand Recursion.

Link to comment
Share on other sites

you are looking to make the queries that execute most run faster or not at all.

Not at all can be done by caching - have you enabled that in admin ?

Faster, but adding indexes.

The fields that are used in the where clauses and the sort order that is set for a particular table should be indexed.

If you enable logging (to a file on your server), you can see which queries execute and how long they take to execute, you then add indexes to speed them up. The index is basically creating a list that the database uses to figure out if particular values exist and has the pointer to the actual database record.

To give a stupid example, should you have customers browsing your catalog often by manufacturer, if you have an index on the manufacturer field, it will find them all pre-sorted by manufacturer so if you are looking for manufacturer A and system sees B, it knows not to look further as all items from manufacturer A have been found.

 

HTH

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

I had the same issue, http://www.bscexpress.com , it took a lot of optimization of our categories.php and the backend SQL system, I indexed pretty much everything that was important.

 

Products_model

Products_quantity

Price

categories

everything. Thats not allways the best thing to do, but I have literally another 50000 to add to our web site, and I dont use easy populate anymore as it is too slow.

I use MSACCESS and custom queries (written for the task)

 

The web server should not be the issue as I have run ours on an hp netserver e60 dual pentium III 300 mhz.

Link to comment
Share on other sites

So I should have Use Cache turned to true? And the files should go to the temp folder? By doing this, I can look at what is written to temp and see what is taking the most time?

 

 

How does one create an index?

"creating a list that the database uses to figure out if particular values exist and has the pointer to the actual database record."

 

 

 

you are looking to make the queries that execute most run faster or not at all.

Not at all can be done by caching - have you enabled that in admin ?

Faster, but adding indexes.

The fields that are used in the where clauses and the sort order that is set for a particular table should be indexed.

If you enable logging (to a file on your server), you can see which queries execute and how long they take to execute, you then add indexes to speed them up. The index is basically creating a list that the database uses to figure out if particular values exist and has the pointer to the actual database record.

To give a stupid example, should you have customers browsing your catalog often by manufacturer, if you have an index on the manufacturer field, it will find them all pre-sorted by manufacturer so if you are looking for manufacturer A and system sees B, it knows not to look further as all items from manufacturer A have been found.

 

HTH

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...