Optimization : Where to focus

[Ben Holness]

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?

[Toby Inkster]

Re: Optimization : Where to focus

Ben Holness wrote:
[color=blue]
> 3. Product code is a varchar in the DB[/color]

Do this. If the column is indexed, this should be very fast.

The only queries that will slow things down are LIKE queries where the
first part of the pattern to match is a wild card.

But for a product code, it's likely you'll only be interested in exact
matches, so that shouldn't be a problem.

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact

[Jerry Stuckle]

Re: Optimization : Where to focus

Ben Holness wrote:[color=blue]
> 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?[/color]

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@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: Optimization : Where to focus

Ben Holness wrote:[color=blue]
> 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?[/color]


Oops - pushed send too quickly.

However, for only 500 items I'd say just use the varchar. As Toby said, if you
index this column (automatic if it's the primary key), it should be very fast.


--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Ben Holness]

Re: Optimization : Where to focus

> However, for only 500 items I'd say just use the varchar. As Toby said, if you[color=blue]
> index this column (automatic if it's the primary key), it should be very fast.[/color]

Based on the first email, I will choose int or enum. I am looking to
squeeze as much efficiency as possible out of the hardware :) I will be
happy shaving off 2 microseconds of cpu processing time.

So which do you think takes the least processor time - PHP looking up a
value in an array or MySQL converting enum to int? ;)

Cheers,

Ben

[Jerry Stuckle]

Re: Optimization : Where to focus

Ben Holness wrote:[color=blue][color=green]
>>However, for only 500 items I'd say just use the varchar. As Toby said, if you
>>index this column (automatic if it's the primary key), it should be very fast.[/color]
>
>
> Based on the first email, I will choose int or enum. I am looking to
> squeeze as much efficiency as possible out of the hardware :) I will be
> happy shaving off 2 microseconds of cpu processing time.
>
> So which do you think takes the least processor time - PHP looking up a
> value in an array or MySQL converting enum to int? ;)
>
> Cheers,
>
> Ben[/color]

Shouldn't be much difference either way.

And I'd still use varchar. The extra processing required to fetch an int id or
the extra work involved with defining SET values just isn't worth it for a 500
row table. 500K rows, yes.

And if you're pushing your hardware that hard that you either need to get
more/better hardware or build your pages in a compiled language such as C and
have a huge amount of memory for caching. Even reading the page from the disk
will take more than 2 microseconds.



--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===