RE: Help with lengthy query? - solved - Now hardware questions

[Jay Drake]

Thanks to all who helped me out. It seems I missed an essential AND
statement in my query. Including it brought me from 32 seconds to about 1-2
seconds and all seems to be doing fairly well. I'm a little concerned at
this point for the work I'm asking the hardware to do.

The server currently deals with http/smtp/pop3/mysql/ssh/ftp and is an
800MHZ PIII with 384mb RAM.

At this point, I really wonder if this is enough to handle my current needs.
The query I was just wrestling with is run several times on a single page
along with a couple other similarly rough queries which involve 3 large
tables and a few smaller tables each. (2 tables with 100,000 records and a
3rd table which has nearly 200,000 records.) When I open the page which
makes these queries while looking at my server's cpu load and such using top
I see the following, somewhat scary, results:

  2:52pm  up 11 days, 22 min,  2 users,  load average: 0.41, 0.19, 0.11
128 processes: 120 sleeping, 7 running, 0 zombie, 1 stopped
CPU states: 81.2% user, 18.7% system,  0.0% nice,  0.0% idle
Mem:   384660K av,  379152K used,    5508K free,       0K shrd,   62344K
buff
Swap:  786200K av,    3080K used,  783120K free                  243692K
cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
 9869 mysql     15   0 17388  16M  2044 S    97.0  4.5  10:19 mysqld

The only thing I have to say for those numbers is 'wow'.

Recommendations? I'm looking around for the motherboard manual presently,
but I believe I can put 1gb of pc133 or 1.5gb of pc100 sdram on there.
Beyond that, I need to look into the possibility of getting a faster socket
type PIII processor.

Jason Drake
jdrake@stripped