Ok,

I've got a fairly small database (<30,000 products) but there
are 60 columns for each row (this is a must).

Each simple find query takes about 3 seconds, which is way
too long.

Query is something like:
SELECT *
FROM blah
WHERE product_model=123 AND product_name=abc LIMIT 1

I'm trying to figure out if putting more than 512 RAM or upgrading
from a celeron 1.8 to a P4 2~ will have a SIGNIFICANT difference
in speed.

I'm leaning towards getting more RAM and possibly upgrading
to another EV1 server and would like to hear your input.

Thanks,
Luc L.

Check swap usage
If use - add more RAM

What does your /etc/my.cnf look like?

Ram can help if your mysql server is being used heavily. Now before you plunk down the change for it, see if you can optimize mysql a bit. It'll save you some money for now and possibly give you some extra room to breathe.

Thanks guys, I figured the problem and it wasn't caused by
MySQL.

Now, generally speaking, does increasing ram from 512 to
1GB or more speed up the overall scans?

I'm guessing it would on very large databases (500+ megs).

Is this correct?

Thanks!
Luc L.

Originally posted by Luc L.
Ok,

I've got a fairly small database (<30,000 products) but there
are 60 columns for each row (this is a must).

Each simple find query takes about 3 seconds, which is way
too long.

Query is something like:
SELECT *
FROM blah
WHERE product_model=123 AND product_name=abc LIMIT 1

I'm trying to figure out if putting more than 512 RAM or upgrading
from a celeron 1.8 to a P4 2~ will have a SIGNIFICANT difference
in speed.

I'm leaning towards getting more RAM and possibly upgrading
to another EV1 server and would like to hear your input.

Thanks,
Luc L.

I would think most of your problem is either how you are doing the query, or how the database is setup. For each column you are doing a WHERE claus from, ie product_model, you should have an index for that column. Have you checked that?

http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html will describe how to show the indexs per each table.

You can create indexs with this...

http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html

We use a custom written mail system which stores mail/account info in a postgres database. The database is around 40gigs in size, and the mail store table has over 300,000 rows. There is around 2000 email users on the system. Our DB server is a dual p3 800mhz with 2gigs of ram and a big SCSI raid array. I can pull any record outa the mail store table in about 1/10 of a second.

So im pretty sure you have something setup wrong...again most likely indexes.

liam