Hi Arnaud, A disk-based temp table is used if you're SELECTing a column [that can be] longer than 255 characters (BLOB/TEXT to MySQL). This is because the in memory HEAP tables don't currently support variable length rows. Using something like LEFT(text_col, 255), if feasible, will get around this problem.
Also could be disk based if the query examines many rows (large temp table), but your tmp_table_size would probably cover that. BTW, 512M is very, very high for tmp_table_size! Do you have enough memory for 512M * number of connections? :-) Matt ----- Original Message ----- From: "Arnaud" Sent: Wednesday, November 19, 2003 2:18 AM Subject: using temporary / using filesort and disk tables > Hi! > > I have a query that allways creates temporary tables to disk (the ratio > created_tmp_disk_tables/created_tmp_tables is close to 0.8, but I think it > is 1 for this particular query). > This query joins 4 tables, groups by a field a orders by another field (or > sometimes an sql variable). > When I analyze it, I get "where used, using temporary, using filesort" for > the first table of the join (whatever the order of the join), and "where > used" for the other ones. > I have only 2000 rows scanned forthe first table, and 1 for the 3 other > ones. > The variables tmp_table_size and max_heap_table_size are both set very high > (~512M). > > I would like to get rid of those disk tables, to improve the performance of > my query. I understand that using a group by and order by on different > fields implies the use of a temporary table. What I don't understand is why > this table is created on disk, and not in memory? Is it because of the > filesort? If yes, how could I get rid of the filesort? > > If this is not clear enough, I can post a sample query and the result of the > explain. > > Thanks in advance! > > Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
