On Sun, Jul 13, 2003 at 08:51:15AM -0700, Henry Hank wrote: > > I have the following SQL. The source table has 8.1 million rows, and the > resulting table will have about 7.9 million rows. I know that's not much of a > decrease, but the logic is correct as I've tested it on smaller sets. The > problem is that when I run with the full set of 8 million rows, it takes about > 2 hours to complete. The source and target tables are all char or tinyint > fields (i.e. fixed length records). > > insert into extract2 > select field1,field2,field3,field4, > if(right(field1,1)='N',mid(field3,2,1),mid(field3,1,1)) as flag, > count(*) as count, > val-min(val_cnt) as cnt1, > if(max(val)<val_cnt,1,0) as cnt2 , > if(max(val)=min(val) and max(val)=val_cnt,1,0) as last, > if(min(val)=1,1,0) as initial > from extract1 > group by field1,field2,field3,field4; > > While this code is running, the temp table that is created to do the summary > grows to 730,662,620 bytes, which is 22 million records at 33 bytes per record. > Why is mysql creating a temp table of 22 million records, when the SOURCE > table is only 8.1 million records? Even if no summary was taking place at all, > I wouldn't expect the temp table to be almost three times the size. >
Is 33 bytes the size of records in extract1? They probably have another size in your temporary table because it has other fields. It shouldn't have more records than the source table, so your record size appears to be more like 92 bytes. If extract2 has smaller records than this, my guess is that MySQL for some reason decides to use ints instead of tinyints in the temp table. It probably allows for NULLs in some fields in your temp table as well. Not much you can do about it, but just to make sure you're aware of this. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
