I have a table about 1.4 billion records in the following format:
mysql> describe _2009all;
+ --------------- + -------------- + ------ + ----- + ----- ---- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ --------------- + -------------- + ------ + ----- + ----- ---- + ------- +
| userId | int (11) | YES | MUL | NULL | |
| type | varchar (50) | YES | | NULL | |
| kind | varchar (50) | YES | | NULL | |
| description | varchar (255) | YES | | NULL | |
| bundleVersion | varchar (255) | YES | | NULL | |
| bundleId | varchar (255) | YES | | NULL | |
| time | bigint (20) | YES | | NULL | |
+ --------------- + -------------- + ------ + ----- + ----- ---- + ------- +
7 rows in set (0.02 sec)
The entire database takes less than 0.4 terabytes, and I have about 1.5 terabytes of free disk space.
I try to prepare data for analysis when I look at what each user does over time. So I run the following statement:
create table sorted2009 AS (select * from _2009all order by userid, time);
The statement (obviously) takes a lot of time, but at some point it runs out of free disk space, and I get the error message "Error writing file error" (ERROR 3 (HY000):
)Any ideas on how I can create my sorted table? Thanks in advance.
Explanation
Martin: there is only 1 section.
: , , , "select * from _2009all order by userid, time" - , . , , , , .
, , . _2009all, . , (, , " " ):
mysql> select * from _2009all order by userId,time limit 2;
...
2 rows in set (25 min 36.48 sec)
, , 25 . - , .
bot43:
:
mysql> explain select * from _2009all order by userid,time;
+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+
| 1 | SIMPLE | _2009all | ALL | NULL | NULL | NULL | NULL | 1384378798 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+
1 row in set (0.05 sec)
mysql> explain select userId,type,kind,description,bundleVersion,bundleId,time from _2009all order by userid,time
+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+
| 1 | SIMPLE | _2009all | ALL | NULL | NULL | NULL | NULL | 1384378798 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+
1 row in set (0.00 sec)
, - , . , .