New answers tagged

3

Your decomposition is not correct, since in R2 you still have dependencies that violates the BCNF, for instance UtilisateurID → Nom (UtilisateurID is not a key of that relation). The problem is that your algorithm is not correct. When you find a dependency X → A that violates the BCNF, you should decompose a relation in two relations, the first with X+, not ...


1

a) .. engine would fetch the list of person_id in the secondary index on birthday_timestamp and then fetch those results from the clustered index .. - Only InnoDB uses clustering by PK, MyISAM uses HEAP tables (no clustering) and the primary key is just another index with all indexes using pointer/offset to the heap to find the right row. b) sequential vs ...


0

when you define primary key on column sql server implicitly define cluster index on that column and indexes are faster when you use integer datatype,alphanumeric is little bit slow searching than integer column. production_order_table:- production_order int(pk) product_id int(fk) define non-cluster index Product_table:- product_id int(pk) product_code ...


4

I don't think that - a default index generation for foreign key columns - would lead to serious problems. It was just a decision taken from the PostgreSQL developers, to leave this choice to each database designer / administrator. We have the choice to either add an index when creating a foreign key or not. If they had taken the opposite decision, then ...


2

Tip 1: PRIMARY KEY(x, id), INDEX(id) -- where `id` is `AUTO_INCREMENT` is perfectly valid. It has the performance advantage of being more efficient when many queries need to find multiple rows WHERE x = 123. That is, it is slightly more efficient than the 'obvious' PRIMARY KEY(id), INDEX(x, id) The only rule about AUTO_INCREMENT (for InnoDB) is that ...


2

A composite key is simply a key that contains two or more columns. A surrogate key is one which is not naturally unique to the data but is added or imposed onto the data for (hopefully) a good reason. Examples can include IDENTITY columns in SQL Server - known as autonumbers sometimes in other products. Perhaps you are storing information about people, ...


3

No. If your table has the InnoDB engine and the PRIMARY KEY is (pet_id), then defining a secondary index as (person_id) or (person_id, pet_id) makes no difference. The index includes the pet_id column as well so values are sorted as (person_id, pet_id) in both cases. A query like the one you have: SELECT pet_id FROM yourtable WHERE person_id = 127 ORDER ...


2

According to the MySQL Documentation on the Clustered and Secondary Indexes How Secondary Indexes Relate to the Clustered Index All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. ...


2

A very common example: Customer table with CustomerID as CLUSTERED PRIMARY KEY Order table with OrderID (PK), CustomerID, OrderDate and some other columns OrderPositions with OrderPositionID (PK), OrderId, ProductID, Amount, Price ... you have to index the Order tables Of course "it depends" is - as nearly always - the correct answer, but the most ...


10

( Please note that the original wording of the Question was: "Is SYSUTCDATETIME a safe clustered index?" ) What exactly do you mean by "safe"? A Clustered Index doesn't need to be unique, so it is "safe" in the sense that it won't break anything. But it can't be a Primary Key since it can't be guaranteed to be unique. Just try the following and you will see ...


1

A foreign key null means that there may not be an entry on the other side. In a master-detail relationship it means a detail can exist without a master.


2

Put a copy of B and C in the range field (as an extra column) and make that your range key like "B_C" and you can still also have separate columns for B and C if you need.



Top 50 recent answers are included