Found a post regarding this in the mailing list archive, but no response to
it:
> Brian Blood brian at macserve.net
> Thu Nov 27 16:56:42 CET 2003
> why is there both a Primary and Unique index on the primary key field on
> these tables?
> aliases
> mailboxes
> messageblks
> messages
>From the CVS this morning (1.17.6.1)
------------
CREATE TABLE messages (
message_idnr bigint(21) NOT NULL auto_increment,
mailbox_idnr bigint(21) DEFAULT '0' NOT NULL,
messagesize bigint(21) DEFAULT '0' NOT NULL,
seen_flag tinyint(1) default '0' not null,
answered_flag tinyint(1) default '0' not null,
deleted_flag tinyint(1) default '0' not null,
flagged_flag tinyint(1) default '0' not null,
recent_flag tinyint(1) default '0' not null,
draft_flag tinyint(1) default '0' not null,
unique_id varchar(70) NOT NULL,
internal_date datetime default '0' not null,
status tinyint(3) unsigned zerofill default '000' not null,
rfcsize bigint(21) DEFAULT '0' NOT NULL,
PRIMARY KEY (message_idnr),
KEY message_idnr (message_idnr),
UNIQUE message_idnr_2 (message_idnr)
);
CREATE TABLE messageblks (
messageblk_idnr bigint(21) NOT NULL auto_increment,
message_idnr bigint(21) DEFAULT '0' NOT NULL,
messageblk longtext NOT NULL,
blocksize bigint(21) DEFAULT '0' NOT NULL,
PRIMARY KEY (messageblk_idnr),
KEY messageblk_idnr (messageblk_idnr),
KEY msg_index (message_idnr),
UNIQUE messageblk_idnr_2 (messageblk_idnr)
) TYPE=MyISAM;
----------------------
The messages table has the following keys defined:
PRIMARY KEY (message_idnr),
KEY message_idnr (message_idnr),
[second key used to be UNIQUE]
And messageblks:
PRIMARY KEY (message_idnr),
KEY message_idnr (message_idnr),
Is there any point to this duplication? Does it give any speed benefits I'm
not aware of? Otherwise it's just using up space and causing another key
write to be required.
[I'm playing around to see if MySQL 4.1's index preloading will give any
gain; so trying to optimise the keys required as much as possible]
Regards,
Mark.
