Description:
Test1:
InnoDB plugin 1.0.9/MySQL 5.1.48
root@test 10:09:51>desc test_plg;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | MUL | NULL | |
| name2 | varchar(30) | YES | | NULL | |
| dd | datetime | YES | | NULL | |
| dd2 | datetime | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
$mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 420689
Server version: 5.1.48-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@(none) 09:49:50>select @@innodb_version;
+------------------+
| @@innodb_version |
+------------------+
| 1.0.9 |
+------------------+
Sesion1：
root@test 09:56:11>select * from test_plg where id =1;
+----+---------+-------+---------------------+---------------------+
| id | name | name2 | dd | dd2 |
+----+---------+-------+---------------------+---------------------+
| 1 | ssdsdsd | sdxss | 2011-03-07 22:03:26 | 2011-03-07 22:03:26 |
+----+---------+-------+---------------------+---------------------+
1 row in set (0.00 sec)
Session2：create a secondary index：
root@test 10:03:47>alter table test_plg add index ind_name(name);
Query OK, 0 rows affected (37.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
Session1：
root@test 10:03:50>select * from test_plg where id =1;
+----+---------+-------+---------------------+---------------------+
| id | name | name2 | dd | dd2 |
+----+---------+-------+---------------------+---------------------+
| 1 | ssdsdsd | sdxss | 2011-03-07 22:03:26 | 2011-03-07 22:03:26 |
+----+---------+-------+---------------------+---------------------+
1 row in set (30.59 sec)
root@(none) 11:15:42>show full processlist;
Id: 420689
User: root
Host: localhost
db: test
Command: Query
Time: 19
State: manage keys
Info: alter table test_plg add index ind_name(name)
*************************** 3. row ***************************
Id: 425388
User: root
Host: localhost
db: test
Command: Query
Time: 16
State: Waiting for table
Info: select * from test_plg where id =1
Test2:
InnoDB plugin 1.1.5/MySQL 5.5.9：
$mysql -uroot --socket /home/dongkai.zmj/mysql-5.5.9-linux2.6-x86_64/run/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17626
Server version: 5.5.9-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@(none) 09:50:21>select @@innodb_version;
+------------------+
| @@innodb_version |
+------------------+
| 1.1.5 |
+------------------+
1 row in set (0.00 sec)
Sesion1：
root@test 10:07:51>select * from test_plg where id=1;
+----+---------+-------+---------------------+---------------------+
| id | name | name2 | dd | dd2 |
+----+---------+-------+---------------------+---------------------+
| 1 | ssdsdsd | sdxss | 2011-03-07 21:51:44 | 2011-03-07 21:51:44 |
+----+---------+-------+---------------------+---------------------+
1 row in set (0.00 sec)
Session2：create a secondary index：
root@test 10:07:56>alter table test_plg add index ind_name(name);
Query OK, 0 rows affected (1 min 47.59 sec)
Records: 0 Duplicates: 0 Warnings: 0
Session1：
root@test 10:08:05>select * from test_plg where id=1;
+----+---------+-------+---------------------+---------------------+
| id | name | name2 | dd | dd2 |
+----+---------+-------+---------------------+---------------------+
| 1 | ssdsdsd | sdxss | 2011-03-07 21:51:44 | 2011-03-07 21:51:44 |
+----+---------+-------+---------------------+---------------------+
1 row in set (1 min 45.17 sec)
root@(none) 11:15:42>show full processlist;
+-------+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------+
| 17626 | root | localhost | test | Query | 6 | manage keys | alter table test_plg add index ind_name(name) |
How to repeat:
CREATE TABLE `test_plg` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`name2` varchar(30) DEFAULT NULL,
`dd` datetime DEFAULT NULL,
`dd2` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ind_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Insert 200 million rows ;
session1:
alter table test_plg add index ind_name(name);
session2:
select * from test_plg where id=1;
mysql -uroot -e "show full processlist";
Description: Test1: InnoDB plugin 1.0.9/MySQL 5.1.48 root@test 10:09:51>desc test_plg; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | MUL | NULL | | | name2 | varchar(30) | YES | | NULL | | | dd | datetime | YES | | NULL | | | dd2 | datetime | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ $mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 420689 Server version: 5.1.48-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@(none) 09:49:50>select @@innodb_version; +------------------+ | @@innodb_version | +------------------+ | 1.0.9 | +------------------+ Sesion1： root@test 09:56:11>select * from test_plg where id =1; +----+---------+-------+---------------------+---------------------+ | id | name | name2 | dd | dd2 | +----+---------+-------+---------------------+---------------------+ | 1 | ssdsdsd | sdxss | 2011-03-07 22:03:26 | 2011-03-07 22:03:26 | +----+---------+-------+---------------------+---------------------+ 1 row in set (0.00 sec) Session2：create a secondary index： root@test 10:03:47>alter table test_plg add index ind_name(name); Query OK, 0 rows affected (37.16 sec) Records: 0 Duplicates: 0 Warnings: 0 Session1： root@test 10:03:50>select * from test_plg where id =1; +----+---------+-------+---------------------+---------------------+ | id | name | name2 | dd | dd2 | +----+---------+-------+---------------------+---------------------+ | 1 | ssdsdsd | sdxss | 2011-03-07 22:03:26 | 2011-03-07 22:03:26 | +----+---------+-------+---------------------+---------------------+ 1 row in set (30.59 sec) root@(none) 11:15:42>show full processlist; Id: 420689 User: root Host: localhost db: test Command: Query Time: 19 State: manage keys Info: alter table test_plg add index ind_name(name) *************************** 3. row *************************** Id: 425388 User: root Host: localhost db: test Command: Query Time: 16 State: Waiting for table Info: select * from test_plg where id =1 Test2: InnoDB plugin 1.1.5/MySQL 5.5.9： $mysql -uroot --socket /home/dongkai.zmj/mysql-5.5.9-linux2.6-x86_64/run/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17626 Server version: 5.5.9-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@(none) 09:50:21>select @@innodb_version; +------------------+ | @@innodb_version | +------------------+ | 1.1.5 | +------------------+ 1 row in set (0.00 sec) Sesion1： root@test 10:07:51>select * from test_plg where id=1; +----+---------+-------+---------------------+---------------------+ | id | name | name2 | dd | dd2 | +----+---------+-------+---------------------+---------------------+ | 1 | ssdsdsd | sdxss | 2011-03-07 21:51:44 | 2011-03-07 21:51:44 | +----+---------+-------+---------------------+---------------------+ 1 row in set (0.00 sec) Session2：create a secondary index： root@test 10:07:56>alter table test_plg add index ind_name(name); Query OK, 0 rows affected (1 min 47.59 sec) Records: 0 Duplicates: 0 Warnings: 0 Session1： root@test 10:08:05>select * from test_plg where id=1; +----+---------+-------+---------------------+---------------------+ | id | name | name2 | dd | dd2 | +----+---------+-------+---------------------+---------------------+ | 1 | ssdsdsd | sdxss | 2011-03-07 21:51:44 | 2011-03-07 21:51:44 | +----+---------+-------+---------------------+---------------------+ 1 row in set (1 min 45.17 sec) root@(none) 11:15:42>show full processlist; +-------+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------+ | 17626 | root | localhost | test | Query | 6 | manage keys | alter table test_plg add index ind_name(name) | How to repeat: CREATE TABLE `test_plg` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `name2` varchar(30) DEFAULT NULL, `dd` datetime DEFAULT NULL, `dd2` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `ind_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk; Insert 200 million rows ; session1: alter table test_plg add index ind_name(name); session2: select * from test_plg where id=1; mysql -uroot -e "show full processlist";