Hello
I am running an online web shop using a MySQL (v5.0.19) backend. The web
app runs really fast whilst the MySQL database is on the local machine
(2x PIII procs, 2GB mem). However I tried moving the database onto a
dedicated database machine (2x P4 Zeon 4Gb Mem Raid5) and it runs really
slow. I have ruled out networking issues, firewalls etc.
I ran one of the more complex (and longer) queries using the local
database. The memory and cpu usage was minimal. I ran the same query
pointed to the remote database and memory usage looked fine but cpu
usage was around 99%. I ran a show processlist whilst the query was
running and saw the following:
mysql> show processlist;
************************ 3. row ***************************
Id: 157
User: www-data
Host: [my_web_server_IP]:36619
db: gb_test
Command: Query
Time: 0
State: Copying to tmp table
Info: SELECT stdep1.* FROM st_des JOIN st_acc ON (st_des.st_des =
st_acc.st_des) JOIN stdep1 ON (st_acc.st
__________________________________________________
This is the query I ran
SELECT st_brd.rank, st_des.st_des, st_des.st_grp,
st_des.st_brd,st_des.www_cat, st_des.model,st_des.descr,
st_acc.st_acc,st_acc.rretail, st_brd.logo_file, st_brd.brd_name
FROM st_des
JOIN st_acc ON (st_des.st_des = st_acc.st_des)
JOIN ps_plu ON (st_acc.st_acc = ps_plu.st_acc)
JOIN st_brd ON (st_des.st_brd = st_brd.st_brd)
WHERE st_des.www_sell='Y' AND st_brd.www_show='Y'
AND st_des.st_brd = '$brand'
GROUP BY st_des.st_des
ORDER BY st_des.st_brd,st_des.st_grp,st_des.st_des
LIMIT 0,12
This query takes 3.65s across the network.
----------------------------------------------------
Here is another query
SELECT stdep1.* FROM st_des JOIN st_acc ON (st_des.st_des =
st_acc.st_des) JOIN stdep1 ON (st_acc.stdep1 = stdep1.stdep1) WHERE
st_des.www_sell='Y' AND stdep1.st_grp = '$group' GROUP BY st_acc.stdep1
ORDER BY stdep1.ww_seq DESC;
This one takes 0.61s
In both cases temporary tables get created. Is it possible to rewrite
the queries so that they do not require tmp tables? How come no tmp
table is created when the query is run on a local database?
Thanks
--
Rhys Johnson <[EMAIL PROTECTED]>
GolfBox
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
