← prev | next →
piterden 2020-09-22 04:36:47
What is reference physically?
drfibonacci 2020-09-22 04:38:20
piterden 2020-09-22 04:36:47
What is reference physically?
By text, we type in the value, which is the same as the PK in column A
But I want the database to be able to keep just a reference to the necessary row in A’s PK, from say multiple rows in B of the FK
piterden 2020-09-22 04:39:08
drfibonacci 2020-09-22 04:38:20
By text, we type in the value, which is the same as the PK in column A
But I want the database to be able to keep just a reference to the necessary row in A’s PK, from say multiple rows in B of the FK
But FK is that text you are talking about
drfibonacci 2020-09-22 04:39:17
piterden 2020-09-22 04:39:08
But FK is that text you are talking about
Yes
piterden 2020-09-22 04:41:35
It takes minimum memory for your soft to know that row of table B having id=5 should be related to the row of table A having id=1
piterden 2020-09-22 04:42:33
Without storing it your soft WILL NOT KNOW IT
piterden 2020-09-22 04:44:39
Btw, Python have to store a very similar identifiers, which are links to a large objects in memory
piterden 2020-09-22 04:45:43
FK is just a link
drfibonacci 2020-09-22 04:47:20
I’m not confused about FK actually, I’m more curious as to why we need to physically store the data in table B for the FK column, when we could just have references to the required instances of table A’s PK
piterden 2020-09-22 04:47:58
If you are going sometime to visit Saint Petersburg then I can explain it to you more detailed ))
piterden 2020-09-22 04:48:15
drfibonacci 2020-09-22 04:47:20
I’m not confused about FK actually, I’m more curious as to why we need to physically store the data in table B for the FK column, when we could just have references to the required instances of table A’s PK
You don’t store the data
drfibonacci 2020-09-22 04:48:17
For any traversal to occur, be it from B to A, or B to B, the indexed PK could be simply used to achieve the same
piterden 2020-09-22 04:48:28
piterden 2020-09-22 04:48:15
You don’t store the data
Only the link
piterden 2020-09-22 04:48:36
FK is not data
drfibonacci 2020-09-22 04:48:38
piterden 2020-09-22 04:48:28
Only the link
Ohh now its clear
piterden 2020-09-22 04:49:31
drfibonacci 2020-09-22 04:48:38
Ohh now its clear
Damn, I already hoped we will meet
piterden 2020-09-22 04:50:18
It’s joke(on the half), sorry
drfibonacci 2020-09-22 04:51:46
piterden 2020-09-22 04:48:36
FK is not data
So just to make things crystal clear
For an ‘id’ column, which is the PK column in table A
And an ‘user_id’ column, which is the FK column in table B
are you saying that the data present in the ‘user_id’ column is simply a link, and this data is not ‘ACTUALLY’ present in the umm.. namespace/storage allocation of table B?
piterden 2020-09-22 04:54:52
Data is the row from table A, and id is the identifier, placed by us there to have an ability to use it as the link to that row (data) from another table
drfibonacci 2020-09-22 04:56:28
piterden 2020-09-22 04:54:52
Data is the row from table A, and id is the identifier, placed by us there to have an ability to use it as the link to that row (data) from another table
Clear sir!! Kanyeshna!!
piterden 2020-09-22 04:56:50
Please don’t call me sir
drfibonacci 2020-09-22 04:57:02
It was a joke too xD
piterden 2020-09-22 04:58:05
drfibonacci 2020-09-22 04:57:02
It was a joke too xD
I know, but it smells slavery. That’s why I don’t like it. Also it wasn’t half-of-a-joke )))
drfibonacci 2020-09-22 05:03:56
@piterden Is it okay to dm you?
piterden 2020-09-22 05:05:48
Sure
vuduong09 2020-09-22 06:26:55
How to download mysql enterprise 5.7 ?
piterden 2020-09-22 06:28:14
vuduong09 2020-09-22 06:26:55
How to download mysql enterprise 5.7 ?
Wat? You don’t know how to download files from inet?
MasterZiv 2020-09-22 07:41:52
drfibonacci 2020-09-22 04:11:32
So I have table A with the primary key column and another table B with a foreign key column
Cardinality is 1..* from A->B
My question is that are the values of the foreign key column stored separately in table B, or they are simply referenced to the existing values of the PK column in table A
Both are true.
MasterZiv 2020-09-22 07:42:28
drfibonacci 2020-09-22 04:23:28
A reference to the PK column, a pointer in terms of C
C is not relevant here
MasterZiv 2020-09-22 07:43:54
drfibonacci 2020-09-22 04:30:44
Any way to manually circumvent this behavior?
Like in python, explicit assignment points to same object in memory
There is no pointers in a relational database
MasterZiv 2020-09-22 07:50:45
drfibonacci 2020-09-22 04:51:46
So just to make things crystal clear
For an ‘id’ column, which is the PK column in table A
And an ‘user_id’ column, which is the FK column in table B
are you saying that the data present in the ‘user_id’ column is simply a link, and this data is not ‘ACTUALLY’ present in the umm.. namespace/storage allocation of table B?
Both.
the data present in the ‘user_id’ column is a link, and this data is ‘ACTUALLY’ present in the storage allocation of table B
drfibonacci 2020-09-22 07:54:33
MasterZiv 2020-09-22 07:50:45
Both.
the data present in the ‘user_id’ column is a link, and this data is ‘ACTUALLY’ present in the storage allocation of table B
Ahh okay okay
drfibonacci 2020-09-22 07:54:56
Thanks to both of you for explaining
drfibonacci 2020-09-22 07:56:27
@MasterZiv Do you think there can be performance improvements and/or storage optimization if this feature was incorporated instead?
drfibonacci 2020-09-22 08:00:28
As an example – say I am querying on the secondary table using the FK and performing some sort of join on the primary table which has its respective PK value currently in memory
Do you think the data access time for this particular scenario improves?
MasterZiv 2020-09-22 10:14:05
drfibonacci 2020-09-22 07:56:27
@MasterZiv Do you think there can be performance improvements and/or storage optimization if this feature was incorporated instead?
Your mistake is that you think the link, or pointer, to the data element is smaller then the data element itself.
This is not true.
As DBMS store really big amount of data, this link, a hypothetical pointer to data element (field or field value) should be really big in size, at least it should be as big as the so called RID (row id in the database) which is really used inside many databases, but should be even bigger, because this is a pointer to the field, an element of a row.
As RID s are usually a C long long (64 bit) value, this field is should be something like 64bit plus, say, one or two bytes more.
If you consider a FK field which is a reference to a PK field, it usually int or big int field or even shorter.
Master Yoda 2020-09-22 14:21:12
drfibonacci 2020-09-22 04:47:20
I’m not confused about FK actually, I’m more curious as to why we need to physically store the data in table B for the FK column, when we could just have references to the required instances of table A’s PK
If that was feasible, they would have done for indexes too. Why would you create an index file and then sort. If possible as you say by just referencing the table itself. Index is also kind of a table holding columns data in order
Master Yoda 2020-09-22 14:31:10
Besides tables are represented as files. ibd and ibdata. I can’t fathom what you are talking is possible.
drfibonacci 2020-09-22 16:01:05
MasterZiv 2020-09-22 10:14:05
Your mistake is that you think the link, or pointer, to the data element is smaller then the data element itself.
This is not true.
As DBMS store really big amount of data, this link, a hypothetical pointer to data element (field or field value) should be really big in size, at least it should be as big as the so called RID (row id in the database) which is really used inside many databases, but should be even bigger, because this is a pointer to the field, an element of a row.
As RID s are usually a C long long (64 bit) value, this field is should be something like 64bit plus, say, one or two bytes more.
If you consider a FK field which is a reference to a PK field, it usually int or big int field or even shorter.
Yes I’ve understood the drawbacks and the difficulties which it would entail
Also it might not be feasible for a rdbms
drfibonacci 2020-09-22 16:02:34
Master Yoda 2020-09-22 14:31:10
Besides tables are represented as files. ibd and ibdata. I can’t fathom what you are talking is possible.
I see, yes you are right
However, table A’s file can exist without B, but vice versa is not possible right?
drfibonacci 2020-09-22 16:05:36
Master Yoda 2020-09-22 14:21:12
If that was feasible, they would have done for indexes too. Why would you create an index file and then sort. If possible as you say by just referencing the table itself. Index is also kind of a table holding columns data in order
That’s for a different reason entirely though, thanks to @piterden I understand that
It’s because of branch predictors and in general – a bst implementation might improve search times, but the data can exist in ASC order in the indexes as well
drfibonacci 2020-09-22 16:07:28
Also, access via row id is the fastest way to access a row so that’s there too
Murali 2020-09-24 16:03:52
Friends, can anyone advise how to migrate db logins from one server to another
piterden 2020-09-24 17:25:51
Murali 2020-09-24 16:03:52
Friends, can anyone advise how to migrate db logins from one server to another
What do you mean saying logins?
acromegale 2020-09-24 17:28:26
i guess he does mean a mysql.user table
piterden 2020-09-24 17:30:28
Vanga
acromegale 2020-09-24 17:30:45
Huyanga
piterden 2020-09-24 17:31:14
You are one guy )))
piterden 2020-09-24 17:33:24
There is also usb-boot ))
Murali 2020-09-24 17:34:50
acromegale 2020-09-24 17:28:26
i guess he does mean a mysql.user table
Yes my request is same
← prev | next →