搜索查询的最佳方式是什么？

Question

我有一个大的SQLite数据库(45MB)。我使用INNER JOIN从不同的表获取数据。然而，它的运行速度非常慢。也许这取决于我正在测试的手机(三星Galaxy Ace)。要使查询速度非常快，应该做些什么？A)为表创建索引b)创建新表，其中将包括我需要的表c)以其他方式推荐

​

​

我使用表:entry、r_ele、k_ele、sense、gloss with JOIN。

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 g.lang IS NULL AND g.value like '"
                + lookingFor + "%'  GROUP BY g.value LIMIT 5 ";

Answer 1

如果您有一个大型数据库，并且在运行时执行了如此多的连接，那么性能肯定会很慢。我也遇到过类似的问题。我解决这个问题的方法是在应用程序初始化时通过连接相应的表来创建具有所需列的临时表。

如果您的数据是动态的，您还需要在需要时更新临时表中的数据。但是，如果更改后的数据量不是很大，那么更新并不会造成任何性能问题。

创建临时表的好处是，较长的操作只存在一次，并且在启动时。