test.txt
1,"one,two or three",100
2,"one,two or three",1234
3,"one,two or three",3
1,"one,two or three",100
4,"one,two or three",1234
5,"one,two or three",1234
3,"one,two or three",3
6,"one,two or three",66
重复以上内容生成8320320行数据。
mysql
mysql> create table ex3(id int(11) not null auto_increment,textfield varchar(20) not null,number int(1
1) not null,index(`id`))engine=innodb default charset=latin1;
Query OK, 0 rows affected (0.39 sec)
mysql> load data infile 'd:/devEnv/Infobright/data/test.txt' into table test.ex3 fields terminated by
',' enclosed by '"';
Query OK, 8320320 rows affected (1 min 57.59 sec)
Records: 8320320 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select id,sum(number) as num from test.ex3 group by id;
+----+------------+
| id | num |
+----+------------+
| 1 | 166406400 |
| 2 | 1026727488 |
| 3 | 4992192 |
| 4 | 1026727488 |
| 5 | 2053454976 |
| 6 | 109828224 |
+----+------------+
6 rows in set (1 min 24.46 sec)
mysql> select sum(number) as num from test.ex3;
+------------+
| num |
+------------+
| 4388136768 |
+------------+
1 row in set (4.12 sec)
mysql> select id,textfield,number from test.ex3 where id=3;
1664064 rows in set (18.06 sec)
mysql> select sum(number) from test.ex3 where id=3;
+-------------+
| sum(number) |
+-------------+
| 4992192 |
+-------------+
1 row in set (15.00 sec)
mysql> select count(*) from test.ex3;
+----------+
| count(*) |
+----------+
| 8320320 |
+----------+
1 row in set (4.92 sec)
mysql infobright
mysql> create table ex3(id int not null,textfield varchar(20) not null,number int not null)engine=brig
hthouse;
Query OK, 0 rows affected (0.08 sec)
mysql> load data infile 'd:/devEnv/Infobright/data/test.txt' into table test.ex3 fields terminated by
',' enclosed by '"';
Query OK, 8320320 rows affected (13.31 sec)
Records: 8320320 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select id,sum(number) as num from test.ex3 group by id;
+----+------------+
| id | num |
+----+------------+
| 1 | 166406400 |
| 2 | 1026727488 |
| 3 | 4992192 |
| 4 | 1026727488 |
| 5 | 2053454976 |
| 6 | 109828224 |
+----+------------+
6 rows in set (0.93 sec)
mysql> select sum(number) as num from test.ex3;
+------------+
| num |
+------------+
| 4388136768 |
+------------+
1 row in set (0.00 sec)
mysql> select id,textfield,number from test.ex3 where id=3;
1664064 rows in set (1.63 sec)
mysql> select sum(number) from test.ex3 where id=3;
+-------------+
| sum(number) |
+-------------+
| 4992192 |
+-------------+
1 row in set (0.32 sec)
mysql> select count(*) from test.ex3;
+----------+
| count(*) |
+----------+
| 8320320 |
+----------+
1 row in set (0.04 sec)