优化表结构或查询 [英] Optimise table structure or query

问题描述

我有一个名为"Society"的表
还有5个主表"BlockA"，"BlockB"，"BlockC"，"BlockD"，"BlockE".

社会"表包含所有5个主表的外键列.

现在，当我编写选择查询时，要花很多时间才能执行，因为5个表正在引用一个表并将这些表联接会导致延迟结果.

  SELECT 协会.*，BlockA.*，BlockB.*，BlockC.*，BlockD.*，BlockE.*
 FROM 协会
 INNER   JOIN  BlockA  ON  Society.ABlock = BlockA.ID
 INNER   JOIN  BlockB  ON  Society.BBlock = BlockB.ID
 INNER   JOIN  BlockC  ON  Society.CBlock = BlockC.ID
 INNER   JOIN  BlockD  ON  Society.DBlock =块ID
 INNER   JOIN  BlockE  ON  Society.EBlock =块E.ID

因此，如何优化我的表结构或查询

解决方案

可能不是联接是问题所在……可能是您选择了每个字段文件.您可以进一步缩小选择语句的范围吗?您可以通过从每个文件中仅选择一个字段来测试它，并查看它可以提高多少速度.

而且，在不了解文件用途，文件打算如何工作以及它们还有哪些其他领域的情况下，很难提出如何解决它的建议.............................................................想知道为什么有五个表.它们大多包含相同的信息吗?如果是这样，那么您应该有一个表，并为其提供一个名为块"的列，其中包含A或B或C或D或E.如果它们各自具有非常不同的信息，则此建议将不起作用. />

I am having a table named "Society"
And 5 master tables "BlockA","BlockB","BlockC","BlockD","BlockE".

The "Society" table contains the foreign key columns of all the 5 master tables.

Now when i write the select query it takes a lot of time to execute, as 5 tables are referencing one table and joining those tables cause in delay result.

SELECT Society.*,BlockA.*,BlockB.*,BlockC.*,BlockD.*,BlockE.*
FROM Society
INNER JOIN BlockA ON Society.ABlock=BlockA.ID
INNER JOIN BlockB ON Society.BBlock=BlockB.ID
INNER JOIN BlockC ON Society.CBlock=BlockC.ID
INNER JOIN BlockD ON Society.DBlock=BlockD.ID
INNER JOIN BlockE ON Society.EBlock=BlockE.ID

So how to optmise my table structure or query

解决方案

It may not be the joins that are the problem...it could be that you are selecting EVERY FIELD FROM EVERY FILE. Can you narrow down your select statement more? You could test it by selecting just one field from each file and see how much it speeds up.

Also, without knowing more about what your files are for, how they are intended to work, and what other fields they have it''s hard to suggest how to fix it....off the top of my head I''m wondering why you have the five tables. Do they mostly contain the same information? If so, then you should have one table and give it a column called "Block" that contains A or B or C or D or E. If they each have very different information then this suggestion will not work.

这篇关于优化表结构或查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！