Reputation: 2710
Some SELECT statements take several seconds to return data and I would like to know if and how I could improve performance. The DB normally is quite small (~10-40MB) but the larger it gets, the longer it takes. One example query which takes very long is the following:
SELECT intf_id FROM interfaces
WHERE intfType IN (SELECT intfType FROM interfaces
WHERE intf_id=39151)
AND macAddress IN (SELECT l2_addr FROM neighbor
INNER JOIN nlink ON nlink.neighbor_neighbor_id=neighbor.neighbor_id
INNER JOIN interfaces ON interfaces.intf_id=nlink.interfaces_intf_id
WHERE interfaces.intf_id=39151)
AND status LIKE 'UP' AND phl=1 AND intf_id <> 39151
Maybe it's because of the nested SELECT statements?
EXPLAIN QUERY PLAN csv:
"0","0","0","SCAN TABLE interfaces USING COVERING INDEX ii1"
"0","0","0","EXECUTE LIST SUBQUERY 1"
"1","0","0","SEARCH TABLE interfaces USING INTEGER PRIMARY KEY (rowid=?)"
"0","0","0","EXECUTE LIST SUBQUERY 2"
"2","0","2","SEARCH TABLE interfaces USING INTEGER PRIMARY KEY (rowid=?)"
"2","1","0","SCAN TABLE neighbor"
"2","2","1","SEARCH TABLE nlink USING COVERING INDEX sqlite_autoindex_nlink_1 (neighbor_neighbor_id=? AND interfaces_intf_id=?)"
Upvotes: 2
Views: 4969
Reputation: 1400
could you try creating an index on interfaces.macAddress and using the following joined Query instead? The subqueries seem to be slower in this case.
SELECT interface_RH.intf_id FROM interfaces AS interface_LH
INNER JOIN nlink ON nlink.interfaces_intf_id = interface_LH.intf_id
INNER JOIN neighbor ON nlink.neighbor_neighbor_id = neighbor.neighbor_id
INNER JOIN interfaces AS interface_RH ON interface_RH.macAddress = neighbor.l2_addr
WHERE
interface_LH.intf_id=39151
AND interface_RH.status LIKE 'UP'
AND interface_RH.phl = 1
AND interface_RH.intf_id <> 39151
AND interface_RH.intfType = interface_LH.intfType
Upvotes: 3