Hi Chris, It doesn't take MySQL any more or less time to update a unique index than a non-unique one. :-)
Hope that helps. Matt ----- Original Message ----- From: "Chris Elsworth" Sent: Wednesday, November 26, 2003 12:14 PM Subject: Unique Index efficiency query > 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]
