Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Indexes to improve store performance


Daemonj

Recommended Posts

Posted

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

Posted

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"

  • 2 months later...
Posted

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
  • 1 year later...
Posted
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?

Posted

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

 

-jared

Posted
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

  • 2 weeks later...
Posted

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

  • 2 weeks later...
Posted

Looks like it was successful.

 

-jared

Archived

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

×
×
  • Create New...