This is why I can permit it being part of a composite key. Since you cannot make the same entry for a different date twice (without it logically being a new entry), the UUID does not need to be globally unique - it needs only be unique for each date. The primary key wouldn't be the UUID+DATE index.Īs a response to comments: The UUID in my case is designed solely for the purpose of avoiding duplicate entries because of bad connections. This question is theoretical, so don't bother with considering regular SELECT performance in this case. Would this not mean that case 2 would perform the insert faster because it can look up the UUIDs segmented into dates? If not, then would it be better to use case 1 for insert speed - and in that case, why? Let's assume your 1 billion rows are equally and sequentially distributed across 2,000 different dates. If not it would require that the laws of physics dictate that two identical rows cannot exist - and as far as I'm informed physics don't play a big role when it comes to the uniqueness of numbers written down somewhere, in binary, on a magnetic disk in a computer. I use the word implicit because at some point, somewhere in the process, the server MUST be checking the value. If you perform an insert the server must do some kind of implicit SELECT COUNT(*) FROM table WHERE DATE = AND UUID = and check if the count is 0 or 1. Each row has a composite unique key consisting of a DATE and a UUID. If you perform an insert the server must do some kind of implicit SELECT COUNT(*) FROM table WHERE UUID = and determine if the count is 0 or 1. Each row has a UUID column which is unique. If I understand correctly, then I assume the following would be true: I have asked some other questions that nobody seems to be able to answer - perhaps because I'm not properly explaining myself - that are related to the above question. I cannot find anything about this in the documentation for INSERT.
I would like to know if there is an implicit SELECT being run prior to performing an INSERT on a table that has any column defined as UNIQUE.
0 kommentar(er)
