I have the following (hypothetical) tables and their relationships (primary keys are in square brackets):
[server_id] [device_id] [sensor_id] [property_id]
SERVER --- 1:n --- DEVICE --- 1:n --- SENSOR --- 1:n --- PROPERTY
| |
| m
| |
| MAPPING [mapping_id]
| |
| n
| |
+ ----- 1:n --- AGENT [agent_id]
They have the following record counts:
SERVER: 10
DEVICE: 10000 for each server
SENSOR: 120000 for devices on each server
AGENT: 150000 for devices on each server
PROPERTY: 440000 for sensors on each server
MAPPING: 450000 for sensors and agents on each server
When there is a need to delete all records belonging to a server (let’s say of server_id 1), the following SQL statements are executed (in that order, each with its own transaction):
delete from MAPPING where mapping_id in (select mapping_id from MAPPING where sensor_id in
(select sensor_id from SERSOR where device_id in (select device_id from DEVICE where server_id = 1))) –- statement 1
delete from PROPERTY where property_id in (select property_id from PROPERTY where sensor_id in
(select sensor_id from SENSOR where device_id in (select device_id from DEVICE where server_id = 1))) –- statement 2
delete from AGENT where agent_id in (select agent_id from AGENT where device_id in
(select distinct device_id from DEVICE where server_id = 1))) –- statement 3
delete from SENSOR where sensor_id in (select sensor_id from SENSOR where device_id in
(select device_id from DEVICE where server_id = 1)) –- statement 4
delete from DEVICE where device_id in (select device_id from DEVICE where server_id = 1) –- statement 5
delete from SERVER where server_id = 1 -- statement 6
The first 3 statements completed fairly quickly, however, the statement 4 takes VERY SIGNIFICANTLY longer time to execute, which is puzzling, especially comparing it to statement 3, the latter actually has more records to delete, and the execution plan according to “explain” for practically identical (only that statement 3 with more rows/slightly higher cost).
Anyone can shed some light on this behavior, or suggestions on how statement 4 can be rewritten, with better performance (there is already an index in PROPERTY table on its foreign key sensor_id)?
Thanks