Search query design for two-way dictionary app

Question

I'm currently developing a two-way dictionary application for Android using SQLite + Room. By two-way, I mean a user can search in either language and obtain relevant results.

A key feature I would like to implement is to allow the user to not have to toggle the current language being used to search. E.g. they shouldn't have to press a button to search in Language X or Language Y.

Unfortunately, at least to my limited knowledge, this means I have to conduct search using only one query.

My database is currently set up as follows (I can change schema if required):

The number of rows in Words is ~129,000 and Glosses is ~150,000.

Here gloss means translation with each word have multiple glosses, and pos means part of speech. All fields are TEXT except ids. I am using Room to access my database.

Currently I have a very (I assume to be at least) naive bit of SQL in my DAO that gets me what I want:

@Transaction
@Query("SELECT * FROM words WHERE word LIKE  :searchQuery || '%' " +
        "UNION " +
        "SELECT words.* FROM words  INNER JOIN glosses ON words.id = glosses.word_id WHERE glosses.gloss LIKE  :searchQuery || '%' ")
DataSource.Factory<Integer, WordWithGlosses > getWordWithGlosses(String searchQuery);

The issue is that this is way too slow with most queries taking at least 2-3 seconds which is not acceptable (pretty sure its because of the UNION but unsure how to get what I want without it).

In more detail, the goal is get a row from the words table that has a gloss that matches searchQuery. Then Room creates a WordWithGlosses object using the template:

public class WordWithGlosses {

@Embedded
public Word word;
@Relation(
        parentColumn = "id",
        entityColumn = "word_id"
)
public List<Gloss> glosses;

public WordWithGlosses(Word word, List<Gloss> glosses) {
    this.word = word;
    this.glosses = glosses;
}

public Word getWord() {
    return word;
}

public List<Gloss> getGlosses() {

    return glosses;
}

public String getGlossesPreview() {

    StringBuilder sb = new StringBuilder();

    for (Gloss gloss : glosses) {

        sb.append(gloss.getGloss());
        sb.append("\n");

    }

    return sb.toString();
}

}

I then use this object to populate my RecyclerView (each entry corresponding to one of these objects). Moreover, if the user searches for word X then the relevant entry appears in my RecyclerView. Also if they search for one of the many glosses of word X, the same entry appears.

Two questions:

Should I be even using Room at all for an application that will NEVER change the database? I suspect the object mapping approach that Room uses likely slows things down. I mainly used it because most Android tutorials use it, but I am now questioning this decision. However it is very convenient to use compared to SQLiteHelper + cursors.
Is there an obvious ways to improve the speed of this Query. I am aware of indexing (I currently have an index on words.word both columns of glosses. I am also aware of FTS4 but after running some test queries it does not significantly improve the performance if I still keep the same UNION approach.

Thanks.

forpas · Accepted Answer · 2021-03-06T17:48:22.380

You can write the query without the use of UNION like this:

SELECT w.* 
FROM words w LEFT JOIN glosses g 
ON w.id = g.word_id 
WHERE w.word LIKE :searchQuery || '%' OR g.gloss LIKE :searchQuery || '%'

I assume that words.id is the primary key of words, so you may need an index for glosses.word_id.
Also you can read The LIKE Optimization to test and try to optimize the conditions in the WHERE clause.

I have never used ROOM so I don't know its pros and cons, but if as you say the application will NEVER change the database then I can't find a reason to add an extra layer between your app and the database.
Try SQLiteOpenHelper class.

Search query design for two-way dictionary app

1 Answers1