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]