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
