81–90 of 182 posts
Earlier quoted context omitted.
An int is either 4 or 8 bytes, it's still going to perform better no matter how you treat the GUID. edit: bytes
> An int is either 4 or 8 bits This perfectly summarizes the bitwise-innumeracy of the argument against UUIDs. Even without 128 bit word sizes, most UUIDs are going to be non-matches in their lower bits, assuming a random distribution. There's no computational efficiency gained in the vastly wide critical path here.
Earlier quoted context omitted.
Not really a fan. When your table name is "reservation", you're prefixing "id" with the table name ("reservation_id"), but you don't do the same for the other columns. I've never liked the inconsistency of having "reservation_id", but other columns like "name" instead of "reservation_name". Especially on longer table names where you wind up with columns like "this_really_long_table_id". All just to shorten join claus…
Personally, for me it makes sense that the id field has a different distintive name for each table because it's a specific kind of id, i.e., customerID is not interchangeable with reservationID. For me, this is not so much about prepending the field with the table name, but about calling things by their name. "id" is just too vague, even if you use it in the context of a specific table.
I fail to see how
customer.id = product.id
customer.customerID = product.productIDEarlier quoted context omitted.
An int is either 4 or 8 bytes, it's still going to perform better no matter how you treat the GUID. edit: bytes
> An int is either 4 or 8 bits This perfectly summarizes the bitwise-innumeracy of the argument against UUIDs. Even without 128 bit word sizes, most UUIDs are going to be non-matches in their lower bits, assuming a random distribution. There's no computational efficiency gained in the vastly wide critical path here.
But you can't assume a random distribution because anyone using UUID's for database keys isn't using random UUID's, they're using sequential ones.
Earlier quoted context omitted.
> An int is either 4 or 8 bits This perfectly summarizes the bitwise-innumeracy of the argument against UUIDs. Even without 128 bit word sizes, most UUIDs are going to be non-matches in their lower bits, assuming a random distribution. There's no computational efficiency gained in the vastly wide critical path here.
> most UUIDs are going to be non-matches in their lower bits, assuming a random distribution But you can't assume a random distribution because anyone using UUID's for database keys isn't using random UUID's, they're using sequential ones.
Earlier quoted context omitted.
I actually prefer just using id as the primary key, and I like the explicitness of seeing the table/alias before the column in complex queries. I don't care too much about typing it out; reservation.id isn't longer than reservation_id, and the savings of USING vs ON seem minimal. I also don't care about deduplicating that one field, as I'll likely need to consider other duplicate fields in the results, like created_a…
When you have to join on composite PKs, it's easier to appreciate the parent's advice.
EDIT: I guess that doesn't really address your point of needing to join on a composite natural key though.
Earlier quoted context omitted.
> most UUIDs are going to be non-matches in their lower bits, assuming a random distribution But you can't assume a random distribution because anyone using UUID's for database keys isn't using random UUID's, they're using sequential ones.
Which would still make the low-order quadword not match.
Tip for SQL users: If you give all your ID fields unique names, e.g. by calling your field "reservation_id" instead of "id", even in the reservation table, you can do stuff like: SELECT * FROM reservation JOIN guest USING (reservation_id); By doing "USING (reservation_id)" instead of "ON reservation.id = guest.reservation_id", the field will be automatically deduplicated, so you don't have to qualify it elsewhere in…
The database already knows that guest.reservation_id is a foreign key referencing reservation.id. Why should you have to repeat yourself?
This should be called something like database designs.
Earlier quoted context omitted.
This is a non-issue. Most db engines just append new data rows at the end of the data table and assign an internal row ID for it. The keys (UUID in this case) are stored in the separate index pages using B+ tree, which searches random key (UUID) or sequentially incremented key equally well. UUID key is a problem only if your main query is a range query on the PK of a clustered indexed table. If your main query is a r…
Actually only postgresql does it this way (by storing data in the heap and not in the primary index). Mysql(innodb),mssql,oracle uses the store the row in the primary-key. Edit: I'm ~wrong, see below.
MSSQL allows clustered indexed table as an option to order the physical storage of rows. Oracle has index-organized table as an option.
Edit: They don't use clustered indexed table by default because record insertion is very expensive since clustered index forces the table to store the records contiguously in the index's order. Also Innodb is not truely clustered indexed. It only stores records contiguously for one page at the B+ Tree leaf level. Records in different pages are scattered all over even if the index values are sequential.