Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Indexes to improve store performance


Daemonj

Recommended Posts

I have listed below the entries I have in a create_indexes.php file that I import via PHPMyAdmin's sql feature anytime that I create a new osC store.

 

create_indexes.php

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);

"Great spirits have always found violent opposition from mediocre minds. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence." - A. Einstein

Link to comment
Share on other sites

Its not up to date but it will give you a very nice idea of what can also be improved:

 

http://www.oscommerce.com/community/contributions,491

 

(It's a list of all indices one could/should place on the db for better performance)

 

Mattice

"Politics is the art of preventing people from taking part in affairs which properly concern them"

Link to comment
Share on other sites

  • 2 months later...

hmm that document isn't really descriptive...

 

are those single columns indexes or multiple "column" indexes?

 

another thing i noticed some indexes included fields that where primary keys... there should be no reason to ad a primary key as index since primary keys are indexed anyways...

Designrfix.com | Graphic Design Inspiration & Web Design Resources - @designrfix
Link to comment
Share on other sites

  • 1 year later...
hmm  that document isn't really descriptive...

are those single columns indexes or multiple "column" indexes?

another thing i noticed some indexes included fields that where primary keys... there should be no reason to ad a primary key as index since primary keys are indexed anyways...

 

Ive been going back through the Tips and Tricks forum (some great info in there too if you go back a bit), and was wondering is Daemonj`s post still valid?

ie I presume MS2.2 the current downloadable doesnt have these indexes included?

Link to comment
Share on other sites

If I remember correctly, a couple of them are in there, but certainly not all of them.

 

-jared

 

Hi Jared,

Thanks for your reply.

FYI the 6 mentioned in the first post of this thread arent in the current MS2.2, I added them and its now running a few % quicker :D

Link to comment
Share on other sites

  • 2 weeks later...

hey guys i just ran 6 entries in sql and this is what it says

 

Your SQL-query has been executed successfully

SQL-query:

ALTER TABLE categories ADD INDEX ( sort_order ) ;# Affected rows:27

ALTER TABLE customers ADD INDEX ( customers_email_address ) ;# Affected rows:3454

ALTER TABLE orders ADD INDEX ( customers_id ) ;# Affected rows:2103

ALTER TABLE products ADD INDEX ( products_model ) ,

ADD INDEX ( products_price ) ,

ADD INDEX ( products_date_available ) ,

ADD INDEX ( manufacturers_id ) ;# Affected rows:324

ALTER TABLE products_attributes ADD INDEX ( products_id ) ,

ADD INDEX ( options_id ) ;# Affected rows:384

ALTER TABLE products_options ADD INDEX ( products_options_name ) ;# Affected rows:1

 

is everything Ok ?

 

what will this do

 

thankx

Link to comment
Share on other sites

  • 2 weeks later...

Archived

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

×
×
  • Create New...