Is there any reason in not creating FK on big tables?

|
MasterZiv 2022-01-30 19:46:49
Liran 2022-01-30 17:56:36
but this means like doing a downtime

Yes, you can’t alter a table without down time, can you?

MasterZiv 2022-01-30 21:12:12
madno 2022-01-30 17:56:51
Can we estimate a query execution time?

In general, query execution time is random value and can’t be predicted.

But you can judge about quality of a query by its plan and IO it is going to generate, without executing the query

PS 2022-01-30 23:31:20
Well that sure did not work 😅 so at least not for me… He seems to have the same issue
PS 2022-01-30 23:34:22
Would be still interested to know if one of the 933 members here us a windows 64bit and tried to install mysql new… (not upgrade) 😉 so, maybe I could get the msi?
MasterZiv 2022-01-31 00:36:43
PS 2022-01-29 13:00:58
I am beginner for databases. Not for programming. But it seems like, it really does not work. I found 64bit, it was a zip file, but could not install it and then I tried the 32bit, it did not work…

PS, people say, that you can download 32bit MSI and then install either 32 or 64 bit MySQL server for Windows.

I myself have not checked this, but you can try.

MasterZiv 2022-01-31 00:38:23
PS 2022-01-30 23:34:22
Would be still interested to know if one of the 933 members here us a windows 64bit and tried to install mysql new… (not upgrade) 😉 so, maybe I could get the msi?

Unfortunately I don’t have a Win 64 machine to check this

MasterZiv 2022-01-31 06:46:38
Yes I sow it
Liran 2022-01-31 09:10:57
MasterZiv 2022-01-30 19:46:49
Yes, you can’t alter a table without down time, can you?

nope, impossible. but isn’t it possible to let all the connections work as usual , run the alter table and in case there’s a lock kill the other prcess until the alter is done?

MasterZiv 2022-01-31 09:34:30
Liran 2022-01-31 09:10:57
nope, impossible. but isn’t it possible to let all the connections work as usual , run the alter table and in case there’s a lock kill the other prcess until the alter is done?

You can do this , but manually. And this is quite hard to implement.
And, if some connection locks something, and you kill it , it then will be lolling the transaction back for some time, and this also can require some new locks

Liran 2022-01-31 09:42:26
MasterZiv 2022-01-31 09:34:30
You can do this , but manually. And this is quite hard to implement.
And, if some connection locks something, and you kill it , it then will be lolling the transaction back for some time, and this also can require some new locks

Ok thanks for the input 🙂

Liran 2022-01-31 09:43:30
Is there any reason in not creating FK on big tables? I see some tables in my database that have 2mil+ rows that has column that reference to another tables’s ID but they are not FK
Liran 2022-01-31 09:44:02
does FK allocates a lot of space?
MasterZiv 2022-01-31 10:12:11
Liran 2022-01-31 09:43:30
Is there any reason in not creating FK on big tables? I see some tables in my database that have 2mil+ rows that has column that reference to another tables’s ID but they are not FK

no reason

MasterZiv 2022-01-31 10:12:49
Liran 2022-01-31 09:43:30
Is there any reason in not creating FK on big tables? I see some tables in my database that have 2mil+ rows that has column that reference to another tables’s ID but they are not FK

It is just bad DB desingn

M S 2022-01-31 14:06:38
can any one share me the security checklists of mysql database ,.could you plz share me any information related
Suri 2022-01-31 15:33:47
Hello all,
How to setup a limit for temporary table size?
MasterZiv 2022-01-31 19:09:47
Suri 2022-01-31 15:33:47
Hello all,
How to setup a limit for temporary table size?

Why do you need it ?

Suri 2022-01-31 20:04:16
Wanted to know
MasterZiv 2022-02-01 00:54:59
Suri 2022-01-31 20:04:16
Wanted to know

Well, you just do not need this.
There is no any practical use in this

Suri 2022-02-01 06:58:12
Okay
Suri 2022-02-01 07:13:48
Hi people,
Can anyone please explain:
How to do an ADHOC refresh to re-establish the replication, if the master-slave replication down due to issues which cannot be fixed?
M S 2022-02-01 15:12:39
Hi I have table with 24k rows in that I had composite index on different columns and unique index on priority column which is 0 in all rows so when run a query to fetch information about particular user the explain plan show rows scanning 11k but for that user no data is there
M S 2022-02-01 15:12:50
Why this happening
MasterZiv 2022-02-01 15:14:43
M S 2022-02-01 15:12:39
Hi I have table with 24k rows in that I had composite index on different columns and unique index on priority column which is 0 in all rows so when run a query to fetch information about particular user the explain plan show rows scanning 11k but for that user no data is there

Send DDL , the query text, and what is not clear.

MasterZiv 2022-02-01 15:15:32
M S 2022-02-01 15:12:39
Hi I have table with 24k rows in that I had composite index on different columns and unique index on priority column which is 0 in all rows so when run a query to fetch information about particular user the explain plan show rows scanning 11k but for that user no data is there

And the query plan if you have it

litmusv 2022-02-01 15:16:11
I wanted to ask one question.. it is a very basic one of sql.. I made two tables and i made a foreign key.. but the data type of the Foreign key is supposed to be varchar as I have to insert varchar values but the primary key of other table is int.. then how can I make a foreign key if datatypes are different.
MasterZiv 2022-02-01 15:16:46
M S 2022-02-01 15:12:39
Hi I have table with 24k rows in that I had composite index on different columns and unique index on priority column which is 0 in all rows so when run a query to fetch information about particular user the explain plan show rows scanning 11k but for that user no data is there

Query plan PREDICTS number of rows for particular query conditions, and the prediction can be inaccurate

MasterZiv 2022-02-01 15:17:42
litmusv 2022-02-01 15:16:11
I wanted to ask one question.. it is a very basic one of sql.. I made two tables and i made a foreign key.. but the data type of the Foreign key is supposed to be varchar as I have to insert varchar values but the primary key of other table is int.. then how can I make a foreign key if datatypes are different.

this can’t be. Both FK and PK fields must have same datatype

litmusv 2022-02-01 15:18:05
Then how will I insert the varchar values in it?
MasterZiv 2022-02-01 15:19:10
litmusv 2022-02-01 15:18:05
Then how will I insert the varchar values in it?

You shall not insert, because you can’t create table configuration like that

litmusv 2022-02-01 15:23:55
mysql_en-16898.jpg

litmusv 2022-02-01 15:23:55
mysql_en-16897.jpg

litmusv 2022-02-01 15:24:24
In this question how did one make user_type as foreign key when it has varchar values and userid is int type
MasterZiv 2022-02-01 15:35:20
litmusv 2022-02-01 15:24:24
In this question how did one make user_type as foreign key when it has varchar values and userid is int type

user_id is integer in both tables

litmusv 2022-02-01 15:41:22
MasterZiv 2022-02-01 15:35:20
user_id is integer in both tables

So I’d of table users will be the foreign key not user_type?

|