Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table of unique values (domains_unique), with collumn 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 scaleing I've also tested the Unique Index method on a 40mil rows table and it took 45.8 s

share|improve this question
    
On the second table you update 2 indexes while you update 1 on the first. Its obvious why first method is faster. –  Antonis Jun 24 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. –  clickstefan Jun 25 at 9:07

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 Feb 15 '13 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? –  clickstefan Feb 15 '13 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 Feb 15 '13 at 23:06
    
would an ssd or partitions help? I know they usually help reads, but would it make any difference in my case? –  clickstefan Feb 15 '13 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 Feb 18 '13 at 15:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.