MySQL Bugs: #99647: call file->position when necessary in sql

Bug #99647	call file->position when necessary in sql_delete.cc
Submitted:	20 May 2020 13:11	Modified:	22 May 2020 12:02
Reporter:	lou shuai (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	8.0.*	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When tracing the multi-table delete code,  each table will call position() function, no matter if it's necessary in Query_result_delete::send_data.

I think it's better to call the position() function when we need to store in the tempfile, do not call position() if the table can delete row immediately.

How to repeat:
Not a bug, just for performance

Suggested fix:
see the patch

call position when non immediate delete (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: opt_sql_delete.diff (application/octet-stream, text), 784 bytes.

Hi Mr. shuai,

Thank you for your bug report. Also, thank you very much for your patch.

Since I am the original author of the multi-table updates / deletes, I do have couple of questions.

First of all, how would you deal with the necessity for delayed deletion. Simply, these DELETEs do JOINs and if you delete rows that are required for joining another entity, you will loose any possibility in joining other two, three or more tables.

Next, how would you deal with triggers BEFORE or AFTER DELETE ????  Those could change positions so you would finish by deleting wrong rows.

There are other issues, like delayed insertion into temporary tables.

Thanks in advance for your answers.

hi, Milivojevic,

Thank you very much for your explanation! I'm a newbie to MySQL.

Forgive my ignorance ^-^

IMO, the position() is used to fulfill ref in the handler, the table->record[0] already have all
field values, so the trigger can execute correctly.

Hi Mr. shuai,

No problem for being a newbie. I was once MySQL newbie, back in 1997.

First of all, searching by record contents is much slower then jumping to the position.

Second, you did not answer the rest of my questions.

hi, Sinisa Milivojevic

position() not to jump to the record, position() save ref from the table->record,
rnd_pos() jump to the record.

Can you give me a test example of the situation you mentioned?

Hi,

This is a forum for reporting bugs.

Reporters are the ones supposed to provide the repeatable test case(s), not another way around.

Also, for code analysis, if a feedback is required, it is (again) only required from the reporter.

Not a bug.