CODEWITHSUNDEEP
phpmysql
The_asMan
The_asMan

Reputation: 6403

Can this mySQL query be optimized better? Not a mySQL guru but I think I got it right

Basically, I have two tables that have a 1 to 1 relation.
When I delete a row from subset_panel_options I also want to delete the related row in subset_panel_options_list

Here is some of structure for the two tables. There is no need to show the full table.

[subset_panel_options]
->id
->subset_panel_id

[subset_panel_options_list]
->id
->subset_panel_options_id

DELETE subset_panel_options, subset_panel_options_list 
FROM subset_panel_options 
JOIN subset_panel_options_list 
WHERE subset_panel_options.id = subset_panel_options_list.subset_panel_options_id 
AND subset_panel_id = $subsetPanelId

Upvotes: 0

Views: 48

Answers (1)

J. Bruni
J. Bruni

Reputation: 20492

Yes, it can:

DELETE FROM subset_panel_options 
LEFT JOIN subset_panel_options_list
ON (subset_panel_options.id = subset_panel_options_list.subset_panel_options_id)
WHERE subset_panel_options.subset_panel_id = $subsetPanelId

Using LEFT JOIN you ensure you are deleting from "subset_panel_options" even if there is no corresponding match in the subset_panel_options_list table.

You may also want use referential integrity features available in InnoDB engine. In this case, you need to define subset_panel_options_id as a FK (foreign key) in the subset_panel_options_list table, and an "ON DELETE CASCADE" constraint on it, meaning that when rows at subset_panel_options are deleted, the "orphan" rows in subset_panel_options_list should be immediately deleted too.

Upvotes: 1

Related Questions