Re: Outfile Using too much disk space

[Jamie Koc]

Hi Rick,

Yes, the goal is to produce a sorted file.

mysql> show create table event_2010_09_28 \G;
*************************** 1. row ***************************
Table: event_2010_09_28
Create Table: CREATE TABLE `event_2010_09_28` (
`file_id` int(11) NOT NULL DEFAULT '0',
`id` bigint(20) NOT NULL DEFAULT '0',
`cre_date` datetime DEFAULT NULL,
`record_type_id` tinyint(4) NOT NULL DEFAULT '0',
`customer_id` varchar(255) DEFAULT NULL,
`member_id` varchar(10) DEFAULT NULL,
`cid` varchar(240) DEFAULT NULL,
`department_id` varchar(240) DEFAULT NULL,
`member_department_id` int(11) DEFAULT NULL,
`tid` varchar(245) DEFAULT NULL,
`sale` decimal(9,2) DEFAULT NULL,
`total` decimal(10,2) DEFAULT NULL,
`zip` varchar(12) DEFAULT NULL,
`free` tinyint(1) DEFAULT '0',
KEY `idx_event_mid` (`member_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Here is the query that uses up 300G of space to execute:
SELECT customer_id,record_type_id,member_id,cre_date
INTO OUTFILE 'test'
FROM event_2010_09_28
WHERE customer_id IS NOT NULL
AND customer_id NOT like '%-%'
ORDER BY customer_id, member_id;

Results of show table status:
Name: event_2010_09_28
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 319451656
Avg_row_length: 120
Data_length: 38555182164
Max_data_length: 281474976710655
Index_length: 1815251968
Data_free: 0
Auto_increment: NULL
Create_time: 2010-09-28 00:36:16
Update_time: 2010-10-01 23:38:40
Check_time: 2010-09-28 00:36:16
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:

Out of curisoity, does MySQL create 2 buffer for this query? One to satisfy the where clause and one to sort? Also, does it create the buffers using max field length (i.e. for customer_id (255 char * 319451656 records ~ 81G ).

Thanks for your help!