Unique insert performance: Primary Key vs Unique Index

Question

I have a table of unique values (domains_unique), with column domain varchar(255), with more than 20 mil records.

What's the fastest way to insert into the table, by keeping the domain unique constraint?

I've decided that the query should be :

INSERT IGNORE INTO domains_table (domain) VALUE ('domain.com')

Should I make domain the primary key or should I make it a unique index?

Primary key method:

CREATE TABLE `domains_unique` ( `domain` varchar(255) NOT NULL PRIMARY KEY (`domain`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE = utf8_general_ci;

Unique index method:

CREATE TABLE `domains_unique` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `domain` varchar(255) NOT NULL PRIMARY KEY (`id`), UNIQUE KEY unique_index (`domain`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE = utf8_general_ci;

Also how would much would changing the CHARSET and COLLATION to ascii_bin affect performance?

obs:

I would use INSERT DELAYED IGNORE INTO, but I need to know if the specific row (domain) was inserted, that also rules out the bulk insert.

UPDATE:

As promised the benchmarks results:

INSERT 5k unique rows with 4.5k new rows into a 1 mil row table, one row at a time:

Primary Key method: 5.7 seconds
Unique Index method: 6.3 seconds

To test scaling I've also tested the Unique Index method on a 40mil rows table and it took 45.8 s

On the second table you update 2 indexes while you update 1 on the first. Its obvious why first method is faster. — Antonios
– Antonios, Commented Jun 24, 2014 at 14:08
Correct @Antonis, just that I didn't know how mysql aranges the data on the hdd, and if it would arrange by the primary key, what overhead the varchar would have over int. — Stefan Rogin
– Stefan Rogin, Commented Jun 25, 2014 at 9:07

G-Nugget · Accepted Answer · 2013-02-15 18:19:30Z

From your question, I'm not completely sure if you're inserting multiple values per query, but you definitely should be. With MyISAM as the engine, there should not be a performance difference between the index being UNIQUE or PRIMARY; MyISAM doesn't treat them differently in this case. If you were using InnoDB, however, there would be a difference since it stores the data in primary key order. If you don't need the id column, removing it and making domain the primary key would help performance.

Changing the collation should help since ascii is much simpler than utf8, but you might want to use ascii_general_ci instead of ascii_bin since domain names are case-insensitive.

One other way to do the queries would be to get the number of rows, INSERT DELAYED, flush the delayed writes, and then get the new row count. The difference in the counts would be the same as the affected rows. However, I don't think this would be significantly faster, but it would make the process more complex.

I agree with this, especially on the MyISAM issue. Once a person does ALTER TABLE tblname DISABLE KEYS and loaded the MyISAM table, Primary and Unique are loaded together. They are not separated like Secondary Keys are separated. I suppose the performance marker would simply be the number of UNIQUE KEYS that could exist alongside the PRIMARY KEY. The fewer UNIQUE KEYs, the faster. The more the UNIQUE KEYs, the slower. +1 !!! — RolandoMySQLDBA
– RolandoMySQLDBA, Commented Feb 15, 2013 at 18:54
from what I've tested the varchar Primary Key seems a little slow after a few million inserts. Is there a better storage engine/method for this table? — Stefan Rogin
– Stefan Rogin, Commented Feb 15, 2013 at 23:00
@clickstefan MyISAM is the way to go. InnoDB stores the data in primary key order, so if you insert a lot of data that isn't in primary key order, the whole table would need to be re-arranged to accommodate the new data. Your queries on this table will all use the index, which shouldn't be any faster on InnoDB. The nature of the data doesn't exactly lend itself to performance since the entire index needs to be scanned for each insert to maintain the unique constraint. — G-Nugget
– G-Nugget, Commented Feb 15, 2013 at 23:06
would an ssd or partitions help? I know they usually help reads, but would it make any difference in my case? — Stefan Rogin
– Stefan Rogin, Commented Feb 15, 2013 at 23:14
@clickstefan It could help. I'm not completely familiar with how MyISAM would handle this situation, but I would think that the actual work for this process would happen in memory. If that is the case, an SSD wouldn't really help since it wouldn't be hitting the disk much. — G-Nugget
– G-Nugget, Commented Feb 18, 2013 at 15:02

Stack Exchange Network

Unique insert performance: Primary Key vs Unique Index

UPDATE:

1 Answer 1

Hot Network Questions

Unique insert performance: Primary Key vs Unique Index

UPDATE:

1 Answer 1

Related

Hot Network Questions