如何快速优化搜索查询工作？

Question

query = "SELECT" + " e.id AS _id," + " ke.id AS ke_id,"
            + " ke.fk as ke_fk," + " ke.value as ke_value,"
            + " re.id AS ke_id," + " re.fk as re_fk,"
            + " re.value as re_value," + " s.id AS  s_id,"
            + " s.fk as s_fk," + " g.id AS g_id," + " g.fk as g_fk,"
            + " g.lang," + " g.value as g_value" + " FROM entry e"
            + "     INNER JOIN k_ele ke ON e.id = ke.fk"
            + "     INNER JOIN r_ele re ON e.id = re.fk"
            + "     INNER JOIN sense s ON e.id = s.fk"
            + "     INNER JOIN gloss g ON s.id = g.fk"
            + " WHERE re.value like '" + hiragana
            + "%' OR g.value like '" + lookingFor
            + "%' OR g.value like '%" + strSpace + lookingFor
            + "%' GROUP BY s.id LIMIT 5 ";

此查询从SQLite数据库中获取数据，并将获取的数据放入列表视图中。即使我设置了限制5，它的运行速度也非常慢。应该怎么做才能让搜索更快呢？

Answer 1

NOt是一位SQLLite专家，但这些都是我从其他数据库的经验中看到的东西。不过，您可能需要体验一下。

首先检查你的索引。接下来，您真的需要使用通配符作为第一个字符吗？这通常会使查询在大多数数据库实现中变慢，因为它不允许您在特定字段上使用索引。我还可以指出，在许多数据库中，UNION或UNION ALL查询比在WHERE类中使用or快得多。

Answer 2

减少where子句的联接数和/或条件数。您可以将所有数据放入内存中，然后使用java将所需的结果集缩减为您想要的结果集。

Answer 3

我认为像这样的复杂处理必须在服务器端完成。