mysql optimizations - review of datatypes

[♥bruyndoncx]

Throughout oscommerce INT is used as a standard datatype for integer values, but a lot of these integer values will remain very small.

Since some fields like languages_id are accessed each time a product is shown in a list or in detail, and assuming an index is used to retrieve the product details, I would expect using an integer datatype that uses less storage space would also mean less space required to store the index, thus more index records on a page and less pages to read for the database.

 

http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

 

SMALLINT and INT are sql standards, TINYINT is even smaller but not standard SQL, which might be an issue if you plan on supporting other databases.

 

if you plan on providing a database migration script from 2.3 to 2.4, you might consider a configuraton/installation option where you decide on how many categories, products, languages etc you want to support and set the datatypes accordingly.

 

Food for thought :)

[tgely]

The core base use a global reference where we take into consideration number of world languages and so on. If the number of the world languages more than tinyint can use (256 integer value) rather int should be used etc..

 

If somebody diverge the own shop from the main stream could be advantages in short period of time but later in the future would be able into conflict with next releases. This is my experiment.

Regard of table sizes I suggest orders_status_history or products_description optimatization where you can reach effective space reduction.