2
votes
1answer
1k views

Unique insert performance: Primary Key vs Unique Index

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 ...
4
votes
1answer
332 views

Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
4
votes
3answers
210 views

Most efficient (or meaningful) way to index grid-based data

I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have ...
2
votes
2answers
814 views

How can an identity primary key index become fragmented?

From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered. Example: ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
7
votes
4answers
336 views

What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?

I have several tables where records can be uniquely identified with several broad business fields. In the past, I've used these fields as a PK, with these benefits in mind: Simplicity; there are no ...