You can see it is doing a table scan on TMPTBL. So please create an index on TMPTBL (on columns report_id, sublotno) and run the explain again. Check if the final costvalue goes down from 862630.
-----Original Message----- From: Keith [mailto:[EMAIL PROTECTED] Sent: Friday, Mar 31, 2006 15:20 PM To: Kakhandiki, Ashwath; 'Keith Arnold'; maxdb@lists.mysql.com Subject: RE: Horrible Query Performance on Update Tablename Column_or_Index Strategy PageCount T table scan 42 D deviceinfo_idx join via indexed column 379618 No temporary results created Result is copied, costvalue is 862630 -----Original Message----- From: Kakhandiki, Ashwath [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 4:47 PM To: Keith Arnold; maxdb@lists.mysql.com Subject: RE: Horrible Query Performance on Update Hi Keith, What is the result of this explain statement? Explain SELECT T.SUBLOTNO FROM TMPTBL T, DEVICEINFO D WHERE T.REPORT_ID = D.REPORT_ID I think it might help if you create an index on TMPTBL (report_id, sublotno). Thanks, Ashwath Kakhandiki MAXDB Dev. Support SAP Labs LLC, Palo Alto -----Original Message----- From: Keith Arnold [mailto:[EMAIL PROTECTED] Sent: Friday, Mar 31, 2006 14:36 PM To: maxdb@lists.mysql.com Subject: Horrible Query Performance on Update Friendly and Helpful List Members, We are running MaxDB 7.5.00.24 on an X86 machine w/ 2x3Ghz CPU, 2G ram, SCSI Mirrored drives, Win2K3 Std. Data cache and catalog hit rates are above 99.9%. I have 2 tables: DEVICEINFO (report_id int, groupnum int) approx 27,000,000 records (2.8GB), no keys but an index on report_id There are approx 1000 records per unique report_id. TMPTBL (report_id int, sublotno int) approx 200 records, no keys, no index When I run the SQL UPDATE DEVICEINFO D SET GROUPNUM = (SELECT SUBLOTNO FROM TMPTBL T WHERE T.REPORT_ID = D.REPORT_ID) The data base drops to it's knees and eventually times out. I tried adding WHERE REPORT_ID IN (SELECT REPORT_ID FROM TMPTBL) To the bottom the update query but it makes no difference. Does anyone have any thoughts on why this query is so horribly slow, how I might rewrite it, or what I might do to tune the DB? Your assistance and insight will be greatly appreciated. Regards, Keith Arnold -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
