List:General Discussion« Previous MessageNext Message »
From:Matt W Date:November 27 2003 12:44am
Subject:Re: Unique Index efficiency query
View as plain text  
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

Thread
Unique Index efficiency queryChris Elsworth26 Nov
  • Re: Unique Index efficiency queryMatt W27 Nov
    • Re: Unique Index efficiency queryChris Elsworth27 Nov