|
|
Posted by Jerry Stuckle on 05/03/06 14:40
Ben Holness wrote:
> Hi all,
>
> I am writing a php/mysql application in which I have to deal with around
> 500 product codes.
>
> When a product code is referenced a table with the details of all product
> codes is accessed.
>
> I am not sure how best to optimize this code.
> I am anticipating very high levels of activity for this particular part of
> the application (it's a machine to machine interface).
>
> Clearly the product id should be the primary key, but is it best to be
> int, enum or varchar?
>
> Here are the options:
>
> 1. Product code is represented by an int in the DB and is mapped to the
> actual product code in a PHP array
>
> 2. Product code is an enum in the DB. The enum has all 500 odd product
> codes in it
>
> 3. Product code is a varchar in the DB
>
> Which would be best? Does php speed beat mysql speed in this scenario?
>
> Ben
>
> PS. Extrapolating further, is it best to use PHP or MySQL to, for example,
> sort a dataset, format a date, make a calculation etc?
Ben,
500 rows in a table is very small. I doubt you'll notice any difference at all,
no matter which you use.
Now, if you have 50,000,000 rows, that would be different. In that case I would
definitely recommend using integer primary keys.
The reason is simple - efficiency. It is much faster for the hardware to
compare two integers than two strings, especially if they are VARCHAR and by
definition case insensitive. Integer, OTOH, is the natural value for the
processor, and can be compared in one machine language instruction.
My next choice would be enum, because the enum is stored in the database as an
int. When you select via the enum, MySQL first converts the enum to an integer,
than does integer comparison.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|