mysql load data 主键_先 LOAD DATA 再加索引(主键)是不是真的好？

#### ENV

OS:RHEL 5U4

MYSQL: 5140

Engine: Innodb

#### Create Table ;

CREATE TABLE `http_auth1` (

`username` varchar(64) NOT NULL,

`pass` varchar(200) DEFAULT NULL,

`comment` varchar(200) DEFAULT NULL,

`uid` int(11) NOT NULL,

`gid` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#### LOAD DATA INFILE

# Attention: Do not load data for "username " after sorted;

root@127.0.0.1 : test 16:47:36> load data infile '/var/smack-data/words.dat' into table http_auth1 FIELDS TERMINATED BY ',';

Query OK, 20000000 rows affected, 65535 warnings (13 min 24.49 sec)

Records: 20000000 Deleted: 0 Skipped: 0 Warnings: 1956829

####CP a table

root@127.0.0.1 : test 17:01:04> create table http_auth1_cp engine=innodb as select * from http_auth1;

Query OK, 20000000 rows affected (12 min 50.35 sec)

Records: 20000000 Duplicates: 0 Warnings: 0

#### 2 Table have same size of datafile;

-rw-rw---- 1 mysql mysql 8690 Nov 3 17:02 http_auth1_cp.frm

-rw-rw---- 1 mysql mysql 8665432064 Nov 3 17:13 http_auth1_cp.ibd

-rw-rw---- 1 mysql mysql 8690 Nov 3 16:47 http_auth1.frm

-rw-rw---- 1 mysql mysql 8665432064 Nov 3 16:59 http_auth1.ibd

root@127.0.0.1 : test 17:16:16> show table status like 'http_auth1'G

*************************** 1. row ***************************

Name: http_auth1

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 20041954

Avg_row_length: 426

Data_length: 8538554368

Max_data_length: 0

Index_length: 0

Data_free: 5242880

Auto_increment: NULL

Create_time: 2010-11-03 16:47:18

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.29 sec)

root@127.0.0.1 : test 17:16:18> show table status like 'http_auth1_cp'G

*************************** 1. row ***************************

Name: http_auth1_cp

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 20107025

Avg_row_length: 424

Data_length: 8538554368

Max_data_length: 0

Index_length: 0

Data_free: 5242880

Auto_increment: NULL

Create_time: 2010-11-03 17:02:09

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.06 sec)

#### Add primary key , need more time because of sort;

root@127.0.0.1 : test 17:17:20> alter table http_auth1_cp add primary key (username);

Query OK, 20000000 rows affected (34 min 30.39 sec)

Records: 20000000 Duplicates: 0 Warnings: 0

#### After Add primary key , need more diskspace because of Index Split;

-rw-rw---- 1 mysql mysql 8690 Nov 3 17:17 http_auth1_cp.frm

-rw-rw---- 1 mysql mysql 13820231680 Nov 3 17:54 http_auth1_cp.ibd

-rw-rw---- 1 mysql mysql 8690 Nov 3 16:47 http_auth1.frm

-rw-rw---- 1 mysql mysql 8665432064 Nov 3 16:59 http_auth1.ibd

#### The most important question ?

select count(*) from http_auth1_cp ;

#### It is so slowly Run above Sql ,Speed about 60 mins ;

# Iostat display the speed of readding from disk just 2000Kb/s ;

# But is so fast for SQL on http_auth1 :

select count(*) from http_auth1 , (have not add primary key) ;

#### But,Create a New table from sorted table (http_auth1_cp), no such problem;

create table http_auth1_cp2 as select * from http_auth1_cp;

alter table http_auth1_cp2 add primary key (username);

select count(*) from http_auth1_cp2;

### Speed is Normal; 30 sec

#### After Optimize slow Table http_auth1_cp , read speed change to Normal

root@127.0.0.1 : test 18:11:23> optimize table http_auth1_cp ;

+--------------------+----------+----------+-------------------------------------------------------------------+

| Table | Op | Msg_type | Msg_text |

+--------------------+----------+----------+-------------------------------------------------------------------+

| test.http_auth1_cp | optimize | note | Table does not support optimize, doing recreate + analyze instead |

| test.http_auth1_cp | optimize | status | OK |

+--------------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (1 hour 6 min 12.96 sec)

-rw-rw---- 1 mysql mysql 8690 Nov 3 18:12 http_auth1_cp.frm

-rw-rw---- 1 mysql mysql 8560574464 Nov 3 19:19 http_auth1_cp.ibd

root@127.0.0.1 : test 19:18:52> select count(*) from http_auth1_cp;

+----------+

| count(*) |

+----------+

| 20000000 |

+----------+

1 row in set (28.62 sec)

#### The result

A table , after create primary key on nonsorted column, select count(*) is very slow; why ?

too much Pages ?

different expalin plan ?

Searching ....