CODEWITHSUNDEEP
sqlite
Srv19
Srv19

Reputation: 3618

What is the fastest way of selecting by a list of strings in sqlite database?

I have database with with roughly following structure:

table1 (name) -< table2 -< table3 (score)

where -< means 1 to many relationship. What i need to do is for every string in a given list find the linked entry from table3 with a maximum score value. The way i do it now is quite slow, and i wonder of it could be sped up.

How i am doing this:

SELECT k.score,k.yaw,k.pitch,k.roll,k.kp_number,k.ke_number,k.points,k.elems --various fields of third table
FROM File 
JOIN FaceDetection AS d ON d.f_id=File.file_id --joining second table
JOIN FaceKey AS k ON k.face_det=d.fd_id --joining third table
WHERE name=:fld 
ORDER BY k.score DESC

I open transaction, prepare query with the above text, and in cycle retrieve the entries i am interested in from the database, then commit transaction. What are better, faster ways?

Upvotes: 0

Views: 152

Answers (1)

CL.
CL.

Reputation: 180070

Indexes can be used for all the columns that are used for lookups or sorting, but a query cannot use more than one index per table.

Check the EXPLAIN QUERY PLAN output to see whether this query does table scans or uses indexes.

You are not returning values from any table but FaceKey, so you do not actually need to do a join. However, rewriting the query as below might or might not help:

SELECT score,
       yaw,
       pitch,
       roll,
       kp_number,
       ke_number,
       points,
       elems
FROM FaceKey
WHERE face_det IN (SELECT fd_id
                   FROM FaceDetection
                   WHERE f_id IN (SELECT file_id
                                  FROM File
                                  WHERE name = :fld))
ORDER BY score DESC

Upvotes: 1

Related Questions