SQLite DELETE query freezes on moveToFirst()

Question

I'm trying to delete rows in an SQLite database in an Android application.

Here is the query and the Java code calling it:

final Cursor cursor = mDb.rawQuery("DELETE FROM link WHERE version!=? AND sentence IN (SELECT _id FROM sentence WHERE language=?) AND translation IN (SELECT _id FROM sentence WHERE language=?)", new String[]{String.valueOf(versionToConserve), sentenceLanguage, translationLanguage});
cursor.moveToFirst();
cursor.close();

The application freezes on cursor.moveToFirst().

Here is the EXPLAIN QUERY PLAN:

selectid:0, order:0, from:0, detail:SEARCH TABLE link USING INDEX sqlite_autoindex_link_1 (sentence=? AND translation=?),
selectid:0, order:0, from:0, detail:EXECUTE LIST SUBQUERY 0,
selectid:0, order:0, from:0, detail:SCAN TABLE sentence,
selectid:0, order:0, from:0, detail:EXECUTE LIST SUBQUERY 1,
selectid:1, order:0, from:0, detail:SCAN TABLE sentence, 

I thought that maybe the query could be too slow, but after half an hour, it is still stucked here.

I tried to replace DELETE by SELECT, it freezes as well.

I tried the inner SELECT queries alone, they work perfectly.

I tried to replace the inner SELECT queries by (1,2,3) and (4,5,6), it works.

I tried to use JOIN instead of IN (SELECT ...) but it doesn't accept it. It says LIMIT, WHERE or other terms are expected instead of JOIN.

I don't know how to investigate more. Any ideas?

Answer 1

I finally got it to work with this WHERE clause:

version<>?
AND EXISTS (SELECT 1 FROM sentence WHERE language=? AND sentence=_id)
AND EXISTS (SELECT 1 FROM sentence WHERE language=? AND translation=_id)