我如何使该查询运行更快? [英] How would I make this query run faster?

问题描述

如何使该查询运行得更快...?

How would I make this query run faster...?

SELECT    account_id, 
          account_name, 
          account_update, 
          account_sold, 
          account_mds, 
          ftp_url,         
          ftp_livestatus, 
          number_digits, 
          number_cw,
          client_name, 
          ppc_status, 
          user_name 
FROM     
         Accounts, 
         FTPDetails, 
         SiteNumbers, 
         Clients, 
         PPC, 
         Users 

WHERE    Accounts.account_id = FTPDetails.ftp_accountid 
AND      Accounts.account_id = SiteNumbers.number_accountid 
AND      Accounts.account_client = Clients.client_id     
AND      Accounts.account_id = PPC.ppc_accountid 
AND      Accounts.account_designer = Users.user_id   
AND      Accounts.account_active = 'active' 
AND      FTPDetails.ftp_active = 'active' 
AND      SiteNumbers.number_active = 'active' 
AND      Clients.client_active = 'active'    
AND      PPC.ppc_active = 'active'   
AND      Users.user_active = 'active' 
ORDER BY 
         Accounts.account_update DESC

先谢谢您了:)

解释查询结果:

我实际上没有设置任何外键...我试图避免对数据库进行更改，因为必须尽快进行彻底的检查.

I don't really have any foreign keys set up...I was trying to avoid making alterations to the database as will have to do a complete overhaul soon.

只有主键是每个表的ID，例如account_id，ftp_id，ppc_id ...

only primary keys are the id of each table e.g. account_id, ftp_id, ppc_id ...

推荐答案

索引

• 您需要-至少-在JOIN条件下使用的每个字段的索引.

• You need - at least - an index on every field that is used in a JOIN condition.

在WHERE或GROUP BY或ORDER BY子句中出现的字段上的索引在大多数情况下也是有用的.

Indexes on the fields that appear in WHERE or GROUP BY or ORDER BY clauses are most of the time useful, too.

在表中时，JOIn(或WHERE或GROUP BY或ORDER BY)中使用两个或多个字段，这些(两个或多个)字段的复合(组合)索引可能比单独的索引更好.例如，在SiteNumbers表中，可能的索引是化合物(number_accountid, number_active)或(number_active, number_accountid).

When in a table, two or more fields are used in JOIns (or WHERE or GROUP BY or ORDER BY), a compound (combined) index of these (two or more) fields may be better than separate indexes. For example in the SiteNumbers table, possible indexes are the compound (number_accountid, number_active) or (number_active, number_accountid).

布尔值字段(ON/OFF，活动/不活动)中的条件有时会减慢查询速度(因为索引不是选择性的，因此不是很有用).在这种情况下，可以选择对表进行重组(父亲标准化)，但是您可以避免增加复杂性.

Condition in fields that are Boolean (ON/OFF, active/inactive) are sometimes slowing queries (as indexes are not selective and thus not very helpful). Restructuring (father normalizing) the tables is an option in that case but probably you can avoid the added complexity.

除了通常的建议(检查EXPLAIN计划，在需要的地方添加索引，测试查询的变体)，

Besides the usual advice (examine the EXPLAIN plan, add indexes where needed, test variations of the query),

我注意到在您的查询中存在部分笛卡尔积.表Accounts与三个表FTPDetails，SiteNumbers和PPC具有一对多关系.这样的结果是，如果您有1000个帐户，并且每个帐户都与10个FTPDetails，20个SiteNumber和3个PPC相关，则查询将为每个帐户返回600行(10x20x3的乘积).总共60万行，其中重复了许多数据.

I notice that in your query there is a partial Cartesian Product. The table Accounts has a one-to-many relationships to three tables FTPDetails, SiteNumbers and PPC. This has the effect that if you have for example 1000 accounts, and every account is related to, say, 10 FTPDetails, 20 SiteNumbers and 3 PPCs, the query will return for every account 600 rows (the product of 10x20x3). In total 600K rows where many data are duplicated.

您可以将查询分为三个加一的基本数据(帐户和其余表).这样，将仅传输34K行数据(具有较小的长度):

You could instead split the query into three plus one for base data (Account and the rest tables). That way, only 34K rows of data (having smaller length) would be transfered :

Accounts JOIN Clients JOIN Users 
  (with all fields needed from these tables)
  1K rows

Accounts JOIN FTPDetails
  (with Accounts.account_id and all fields from FTPDetails)
  10K rows

Accounts JOIN SiteNumbers
  (with Accounts.account_id and all fields from SiteNumbers)
  20K rows

Accounts JOIN PPC
  (with Accounts.account_id and all fields from PPC)
  3K rows

，然后使用客户端4个查询中的数据显示组合信息.

and then use the data from the 4 queries in the client side to show combined info.

我将添加以下索引:

Table Accounts
  index on (account_designer)
  index on (account_client)
  index on (account_active, account_id)
  index on (account_update)

Table FTPDetails
  index on (ftp_active, ftp_accountid)

Table SiteNumbers
  index on (number_active, number_accountid)

Table PPC
  index on (ppc_active, ppc_accountid)

这篇关于我如何使该查询运行更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！