SQLite，如何在大型表上更有效地进行选择？

Question

你们中有人知道如何使选择更快或更有效吗？问题是，在SQLite上进行此选择需要几个小时以上的时间。我将它与Python上的sqlite3一起使用，因此命令有一些限制

 SELECT C.id, COUNT (L.linea_construccion) 
 FROM Linea L, Predio P, Comunas C 
 WHERE L.calidad_construccion = 1 AND C.id = P.comuna
      AND L.comuna = C.id AND P.avaluo_exento > C.avaluo_promedio 
 GROUP BY C.id 

一个数据库上有3个表，表Linea有9MM行，表Predio有7MM行，表Comunas有250行

表格的格式如下：

Predio。（id INT，comuna INT，avaluo_exento INT）

Linea。（id INT，comuna INT，calidad_construccion INT，linea_construccion INT）

Comuna。（id INT，avaluo_promedio INT）

Answer 1

首先，使用适当的、明确的、标准的、可读的JOIN语法重写查询：

 SELECT C.id, COUNT(*) 
 FROM Linea L JOIN
      Comunas C
      ON L.comuna = C.id JOIN
      Predio P 
      ON C.id = P.comuna AND P.avaluo_exento > C.avaluo_promedio 
 WHERE L.calidad_construccion = 1 
 GROUP BY C.id ;

从以下索引开始：

• Linea(calidad_construccion, comuna)
• Comunas(id, avaluo_promedio)如果“id”是主键，则可能不需要
• Predio(comuna, avaluo_exento)

根据您拥有的“commonas”数量和返回的数量，您可以通过消除外部GROUP BY来进一步优化此查询

Answer 2

您应该始终使用explicit join作为最佳实践，而不是在where条件下使用implicit join

从表的给定模式中，可以尝试以下操作

SELECT 
  C.id, 
  COUNT (L.linea_construccion) 
 FROM Linea L
 join Predio P
 on L.comuna = P.comuna

 join Comunas C 
 on L.comuna = C.comuna

 where L.calidad_construccion = 1
 AND P.avaluo_exento > C.avaluo_promedio 
 GROUP BY 
  C.id