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

Question

你们中有人知道如何更快或更有效地进行选择吗？问题是这个选择需要几个小时以上才能在 SQLite 上进行。我sqlite3在 Python 上使用它，所以命令有一些限制。 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 aprox。表格的格式是：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

您应该始终使用explicit joinfor 最佳实践，而不是使用implicit joinon wherecondition。

从表的给定架构中，您可以尝试以下操作

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 

Answer 2

首先，使用正确、明确、标准、可读的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)

根据您拥有的“communas”数量和返回的数量，您可以通过消除外部GROUP BY.