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:
Category:MySQL Server: DML Severity:S5 (Performance)
Version:8.0.* OS:Any
Assigned to: CPU Architecture:Any

[20 May 2020 13:11] lou shuai
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
[20 May 2020 13:14] lou shuai
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.

[21 May 2020 12:26] MySQL Verification Team
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.
[21 May 2020 12:56] lou shuai
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.
[21 May 2020 13:02] MySQL Verification Team
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.
[21 May 2020 13:55] lou shuai
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?
[22 May 2020 12:02] MySQL Verification Team

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.