MySQL engines, constraints & keys
I wanted to see how I could improve the performance of a MySQL database with mixed table engines by converting all the MyISAM tables to InnoDB, as well as make the huge DB responsive while backing up by using mysqldump with the --single-transaction option. I used the following PHP script (I know, spare me):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
< ?php
//mysql_connect('127.0.0.1', 'root', 'fubar');
$rsrc = mysql_connect(':/usr/local/tmp/mysql.sock', 'root', 'fubar');
$dbname = 'jstest';
$query = "select table_name from INFORMATION_SCHEMA.TABLES where table_schema = '$dbname' and engine = 'MyISAM'";
echo $query . "\n";
$rs = mysql_query($query, $rsrc);
while ($row = mysql_fetch_array($rs)) {
$tbl = $row[0];
//echo $tbl . "\n";
$sql = "ALTER TABLE $dbname.$tbl ENGINE='InnoDB'";
echo $sql . "\n";
$rs2 = mysql_query($sql, $rsrc);
$rnum = mysql_affected_rows();
echo $rnum . " rows affected\n";
}
|
After looking at the table status following the script run, there was one table which was still set to use MyISAM. In the mysql shell, I tried manually altering the one table to use InnoDB, and then needed a bunch of additional commands to smooth out the DDL problems before MySQL was happy.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
|
mysql> alter table page_info_story_activity engine='InnoDB';
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> desc page_info_story_activity;
+---------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------+------+-----+-------------------+-----------------------------+
| aid | int(11) | NO | PRI | NULL | auto_increment |
| sid | int(11) | NO | PRI | NULL | |
| pid | int(11) | NO | PRI | NULL | |
| status | char(2) | NO | | NULL | |
| date_assigned | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.01 sec)
mysql> show indexes from page_info_story_activity\G
*************************** 1. row ***************************
Table: page_info_story_activity
Non_unique: 0
Key_name: said
Seq_in_index: 1
Column_name: sid
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: page_info_story_activity
Non_unique: 0
Key_name: said
Seq_in_index: 2
Column_name: pid
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: page_info_story_activity
Non_unique: 0
Key_name: said
Seq_in_index: 3
Column_name: aid
Collation: A
Cardinality: 94573
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)
-- the key "said" is an aggregate key formed from the
-- first 3 columns: sid,pid,aid
-- but sid cannot be auto_increment unless it is also primary_key
mysql> alter table page_info_story_activity add primary key (aid);
ERROR 1062 (23000): Duplicate entry '376612' for key 'PRIMARY'
-- there are dupes in aid, so I cannot set it as primary key,
-- b/c primary key is unique by definition.
-- this will remove the auto_increment constraint:
mysql> alter table page_info_story_activity change aid aid int(11) not null;
Query OK, 94573 rows affected (0.20 sec)
Records: 94573 Duplicates: 0 Warnings: 0
-- you have to use all the options from the original DDL without the option
-- that you no longer want. Kind of error prone.
-- now we can add a non-primary key:
mysql> alter table page_info_story_activity add key aid_key (aid);
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- then add back the auto_increment:
mysql> alter table page_info_story_activity change aid aid int(11) not null auto_increment;
Query OK, 94573 rows affected (1.09 sec)
Records: 94573 Duplicates: 0 Warnings: 0
-- finally change to innodb:
mysql> alter table page_info_story_activity engine='InnoDB';
Query OK, 94573 rows affected (0.97 sec)
Records: 94573 Duplicates: 0 Warnings: 0
|
Of course, I edited out a whole mess of trial and error here. The issue was that MySQL wants any column you mark as auto_increment to have its own key, it doesn’t need to be primary; but it can’t be an aggregate which is what it had originally. My solution was to simply add a non-primary key to that column, while keeping the aggregate key. Using a primary key was out of the question since there are duplicate values in the aid column.
EDIT:
Turns out, all I really needed to do was add the individual index, then alter the table engine. Oh well, it was educational. :-)
About The Author
Charlie Herron
Denizen of Portland, Maine; tech jack; lover / hater / whatever; philosophical dabbler. http://twitter.com/realgeek