Can the DB engine determine if b is to be fetched or not, and then optimize the query?

|
2021-07-26 11:12:40
svart 2021-07-26 11:06:27
If you need to split something you can use utility called “split”

Ohk thanks

MasterZiv 2021-07-26 12:34:14
2021-07-26 10:11:39
Bro file size is 6-7gb, i don’t think so any editor or viewer can handle it

no problem

2021-07-26 16:38:12
svart 2021-07-26 11:06:27
If you need to split something you can use utility called “split”

Thanks that’s actually works in linux

rodrigo_tc7 2021-07-26 23:35:17
2021-07-26 07:57:28
I have a 6-7gb .sql file but unable to open it because of it’s huge size. So is there any way to split this file?

Try emeditor

2021-07-27 11:31:14
I am looking to take Mysql 8 certificate any study guide or plan
ColonelMustang 2021-07-27 11:35:42
Does updating a value in a row rewrite the whole row or just the value? I know this might be a stupid question, most likely it will just update the specific column, but I just wanted a confirmation
ayhanayha 2021-07-27 13:04:10
Failed to optimize the fkmbp_users table. Error: Table does not support optimize, doing recreate + analyze instead
MasterZiv 2021-07-27 13:15:13
ColonelMustang 2021-07-27 11:35:42
Does updating a value in a row rewrite the whole row or just the value? I know this might be a stupid question, most likely it will just update the specific column, but I just wanted a confirmation

Just the changed values.
But you must be ignorant about this, you shouldn’t care

ColonelMustang 2021-07-27 13:31:27
MasterZiv 2021-07-27 13:15:13
Just the changed values.
But you must be ignorant about this, you shouldn’t care

yes, thanks

ColonelMustang 2021-07-27 13:32:59
Another question, does running a query like SELECT a FROM (SELECT a, b FROM table) result in fetching b as well? (Even though it is not used in the parent query)
MasterZiv 2021-07-27 13:40:10
ColonelMustang 2021-07-27 11:35:42
Does updating a value in a row rewrite the whole row or just the value? I know this might be a stupid question, most likely it will just update the specific column, but I just wanted a confirmation

Actually, the above is not quite true for Inno DB as it uses MVCC concurrency model, and row versioning .
What must actually happen when a row is changed is Inno should copy the row and create its new version , incrementing hidden version field which is the last field in each clustering index in an InnoDB table.
(which is usually the PK). So, on Inno, the whole row is changed and copied.
But actually you as a DBMS user should not think about this.

MasterZiv 2021-07-27 13:40:32
ColonelMustang 2021-07-27 13:32:59
Another question, does running a query like SELECT a FROM (SELECT a, b FROM table) result in fetching b as well? (Even though it is not used in the parent query)

This is undefined.

ColonelMustang 2021-07-27 13:40:52
MasterZiv 2021-07-27 13:40:10
Actually, the above is not quite true for Inno DB as it uses MVCC concurrency model, and row versioning .
What must actually happen when a row is changed is Inno should copy the row and create its new version , incrementing hidden version field which is the last field in each clustering index in an InnoDB table.
(which is usually the PK). So, on Inno, the whole row is changed and copied.
But actually you as a DBMS user should not think about this.

thanks, any where I can read about this?

ColonelMustang 2021-07-27 13:41:10
MasterZiv 2021-07-27 13:40:32
This is undefined.

undefined how? (or what)

MasterZiv 2021-07-27 13:41:19
ColonelMustang 2021-07-27 13:40:52
thanks, any where I can read about this?

In the docs, in the sources.

MasterZiv 2021-07-27 13:42:37
ColonelMustang 2021-07-27 13:41:10
undefined how? (or what)

The answer to your question is undefined. You cannon neither prove the field is fetched, no that it is not fetched.

ColonelMustang 2021-07-27 13:44:35
MasterZiv 2021-07-27 13:42:37
The answer to your question is undefined. You cannon neither prove the field is fetched, no that it is not fetched.

Ok, let me rephrase it. Can the DB engine determine if b is to be fetched or not, and then optimize the query?

MasterZiv 2021-07-27 13:45:36
ColonelMustang 2021-07-27 13:32:59
Another question, does running a query like SELECT a FROM (SELECT a, b FROM table) result in fetching b as well? (Even though it is not used in the parent query)

Actually, the DBMS reads and writes pages of table rows, so in fact it reads / fetches even neighbor rows, not only fields.
But how the data is actually read is on the responsibility of query plan/optimizer.
So formally it can be anything.

MasterZiv 2021-07-27 13:45:45
ColonelMustang 2021-07-27 13:44:35
Ok, let me rephrase it. Can the DB engine determine if b is to be fetched or not, and then optimize the query?

Yes it can

MasterZiv 2021-07-27 13:46:50
ColonelMustang 2021-07-27 13:44:35
Ok, let me rephrase it. Can the DB engine determine if b is to be fetched or not, and then optimize the query?

But there is no use in writing such queries at all, no use in stating fake subqueries and extra fields.

ColonelMustang 2021-07-27 13:47:42
MasterZiv 2021-07-27 13:46:50
But there is no use in writing such queries at all, no use in stating fake subqueries and extra fields.

of course! This question arose out of a discussion I had elsewhere, the point of which was to determine if the DB engine will skip fetching b altogether

MasterZiv 2021-07-27 13:50:26
ColonelMustang 2021-07-27 13:47:42
of course! This question arose out of a discussion I had elsewhere, the point of which was to determine if the DB engine will skip fetching b altogether

Actually 99% it will skip it.

ColonelMustang 2021-07-27 13:51:26
MasterZiv 2021-07-27 13:50:26
Actually 99% it will skip it.

Can I get a source? (if it’s available on the internet)

MasterZiv 2021-07-27 13:54:03
ColonelMustang 2021-07-27 13:51:26
Can I get a source? (if it’s available on the internet)

Yes you can

ColonelMustang 2021-07-27 13:59:57
MasterZiv 2021-07-27 13:54:03
Yes you can

I mean, where can I read up on this. Is this specifically mentioned in any documentation?

MasterZiv 2021-07-27 14:39:32
ColonelMustang 2021-07-27 13:59:57
I mean, where can I read up on this. Is this specifically mentioned in any documentation?

No, I don’t think this in particular is mentioned anywhere.
This is general principles of architecture of optimizers and interpreters.

ColonelMustang 2021-07-27 14:40:03
Okay
Deb_575 2021-07-27 17:57:37
Can someone tell me how to redirect the output of a line of commands to a log file. I am doing this but the log file generated is not capturing anything instead the output is being displayed on the screen only.

./Xtrabackup –backup –databases=db1 –stream=tar | gzip -> /backupmountpoint/db1_bkp.tar.gz 2> /backupmountpoint/db1bkplog.txt

It’s is creating the backup gz file but it’s not logging the process in the log file.

This is for MySQL database backup job.

Nedko 2021-07-27 19:54:01
did you try with » instead of >
MasterZiv 2021-07-27 20:09:46
Deb_575 2021-07-27 17:57:37
Can someone tell me how to redirect the output of a line of commands to a log file. I am doing this but the log file generated is not capturing anything instead the output is being displayed on the screen only.

./Xtrabackup –backup –databases=db1 –stream=tar | gzip -> /backupmountpoint/db1_bkp.tar.gz 2> /backupmountpoint/db1bkplog.txt

It’s is creating the backup gz file but it’s not logging the process in the log file.

This is for MySQL database backup job.

what is -> around GZIP command ?

MasterZiv 2021-07-27 20:10:51
Deb_575 2021-07-27 17:57:37
Can someone tell me how to redirect the output of a line of commands to a log file. I am doing this but the log file generated is not capturing anything instead the output is being displayed on the screen only.

./Xtrabackup –backup –databases=db1 –stream=tar | gzip -> /backupmountpoint/db1_bkp.tar.gz 2> /backupmountpoint/db1bkplog.txt

It’s is creating the backup gz file but it’s not logging the process in the log file.

This is for MySQL database backup job.

./Xtrabackup –backup –databases=db1 –stream=tar | gzip > /backupmountpoint/db1_bkp.tar.gz 2> /backupmountpoint/db1bkplog.txt

this should be correct one

Deb_575 2021-07-27 20:11:23
Nedko 2021-07-27 19:54:01
did you try with » instead of >

>> I did
It doesn’t work the output is getting displayed on the screen it’s not capturing into the log file

Deb_575 2021-07-27 20:12:01
MasterZiv 2021-07-27 20:10:51
./Xtrabackup –backup –databases=db1 –stream=tar | gzip > /backupmountpoint/db1_bkp.tar.gz 2> /backupmountpoint/db1bkplog.txt

this should be correct one

Tried this too
gzip -> this one is mentioned in percona xtrabackup documentation only

MasterZiv 2021-07-27 21:04:13
Deb_575 2021-07-27 20:12:01
Tried this too
gzip -> this one is mentioned in percona xtrabackup documentation only

This must be error.

Deb_575 2021-07-27 21:50:27
MasterZiv 2021-07-27 21:04:13
This must be error.

Error in what sense? The command is working fine it’s just that the output is not being logged to the specified log file.
Something with redirecting the output it seesms

|