Hello,
Let me just outline the table in question first.
I have a rather large (40,000,000 rows) table as follows:
Table: MessageIDs
Create Table: CREATE TABLE `MessageIDs` (
`mid_msgid` char(96) NOT NULL default '',
`mid_fileid` int(10) unsigned NOT NULL default '0',
`mid_segment` smallint(5) unsigned NOT NULL default '0',
`mid_date` int(10) unsigned NOT NULL default '0',
`mid_bytes` mediumint(8) unsigned NOT NULL default '0',
KEY `fid_msgid` (`mid_fileid`,`mid_msgid`(5)),
KEY `fid_bytes` (`mid_fileid`,`mid_bytes`),
KEY `mid_date` (`mid_date`)
) TYPE=MyISAM PACK_KEYS=1 DELAY_KEY_WRITE=1
Index details:
mysql> show indexes from MessageIDs;
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| MessageIDs | 1 | fid_msgid | 1 | mid_fileid | A |
1114302 | NULL | NULL | | BTREE | |
| MessageIDs | 1 | fid_msgid | 2 | mid_msgid | A |
20057449 | 5 | NULL | | BTREE | |
| MessageIDs | 1 | fid_bytes | 1 | mid_fileid | A |
1114302 | NULL | NULL | | BTREE | |
| MessageIDs | 1 | fid_bytes | 2 | mid_bytes | A |
40114898 | NULL | NULL | | BTREE | |
| MessageIDs | 1 | mid_date | 1 | mid_date | A |
1744126 | NULL | NULL | | BTREE | |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Now, what I want to do with this table is create a unique index on
(mid_fileid, mid_segment).
How does MySQL deal with ensuring that a unique index doesn't end up
non-unique with a table this large? Is making this index going to
proportionally slow down my inserts as the table grows? Would I be
better making it a non-unique index, and doing a select to ensure I'm
not inserting a duplicate?
Thanks for any tips,
--
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
