CODEWITHSUNDEEP
sqlite
mspoerr
mspoerr

Reputation: 2710

How to improve sqlite SELECT performance?

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?

The DB Layout is as follows: DB Layout

EXPLAIN QUERY PLAN Output: enter image description here

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

Answers (1)

Philipp Aumayr
Philipp Aumayr

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

Related Questions