SQLite. How to optimize SELECT sub query?

Question

entry table contains following columns: id, kanji, kana, meaning, compounds. This table has following DDL:

CREATE TABLE entry (
id INTEGER PRIMARY KEY, 
kanji TEXT, 
kana TEXT, 
meaning TEXT,
compounds BLOB);

I want to update the column compounds. This column contains ids of entries which look like to this entry. With updating I do not have problem. I need to optimize SQL query with SELECT sub query. It is very slow (1020ms per query). Example:

SELECT GROUP_CONCAT(ent_com.id) AS comps FROM
(
SELECT * FROM entry
WHERE 
(kanji LIKE '斯う%' OR kanji LIKE '%斯う}' OR kanji LIKE '%{斯う')
 AND 
(kana LIKE 'こう%' OR kana LIKE '%こう}' OR kana LIKE '%{こう')
 AND id!=1004310
) 
ent_com
ORDER BY length(ent_com.[kanji]),length(ent_com.[kana])

Kanji(hieroglyph) and kana(japanese hiragana and katakana alphabets) words start with '{', ends with '}'. I am using JDBC library to connect with SQLite.

SELECT GROUP_CONCAT(ent_com.id) AS comps FROM
(
SELECT * FROM entry
WHERE 
(kanji LIKE ? OR kanji LIKE ? OR kanji LIKE ?)
 AND 
(kana LIKE ? OR kana LIKE ? OR kana LIKE ?)
 AND id!=?
) 
ent_com
ORDER BY length(ent_com.[kanji]),length(ent_com.[kana])

Selecting:

        c.setAutoCommit(false);

        preparedStatementSelect = null;
        preparedStatementSelect = c.prepareStatement(sql_select_entry);

        preparedStatementSelect.setString(1, entry.getKeb_list().get(0)
                + "%");
        preparedStatementSelect.setString(2,
                "%" + entry.getKeb_list().get(0) + "}");
        preparedStatementSelect.setString(3, "%{"
                + entry.getKeb_list().get(0));

        preparedStatementSelect.setString(4, entry.getReb_list().get(0)
                + "%");
        preparedStatementSelect.setString(5,
                "%" + entry.getReb_list().get(0) + "}");
        preparedStatementSelect.setString(6, "%{"
                + entry.getReb_list().get(0) + "%");

        preparedStatementSelect.setString(7, entry.getEnt_seq());

        ResultSet rs = preparedStatementSelect.executeQuery();

How to optimize select subquery to make it faster?

there is no index on kanji and kana columns. only column 'id' is primary key. — Joe Rakhimov, Feb 09 '15 at 08:04
The word boundary searches cannot be optimized with any built-in mechanism. (And the example query does not match the code.) — CL., Feb 09 '15 at 08:29

score 2 · Answer 1 · answered Feb 09 '15 at 08:46

2

There is not much you can do. The dbms has to read every single record in order to find the desired ones, because of the LIKE search. So put your database on a fast drive. That's about it.

You can try to create an index on (kanji,kana,id). With all three relevant columns in the index, SQLite may decide to get all data from the index and not read the table at all. With an SQLite database residing in a single file on disk, I dont know how likely this is to help, though.

answered Feb 09 '15 at 08:46

Thorsten Kettner

89,309
7
49
73

Maybe, I should put all data into FTS, then run the query using that table. What do you think, can this help improve performance? – Joe Rakhimov Feb 09 '15 at 09:22
I am sorry I cannot answer this, as I have no experience with full text search. Maybe you should make this a separate question whether FTS would be appropriate for your data and search criteria. Properly tagged this should give you the answer quickly. – Thorsten Kettner Feb 09 '15 at 10:06

SQLite. How to optimize SELECT sub query?

1 Answers1