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

Question

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?

Your query seems fine. The database does a lot of optimization itself. You transaction call has to be that way too. I see no way to make this more efficient. — André Stannek, Jul 12 '13 at 14:17
can you put the `WHERE` condition as an `AND` condition of the `ON`? — Tobia Zambon, Jul 12 '13 at 14:29
The answer i wanted i eventually got [here][1]. [1]: http://stackoverflow.com/questions/18184392/how-to-get-key-of-the-maximal-record-in-group — Srv19, Aug 19 '13 at 06:04

score 1 · Answer 1 · answered Jul 12 '13 at 14:55

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

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

1 Answers1