带有多个表的MySQL视图导致查询缓慢 [英] Mysql view with join multiple table results in slow query
本文介绍了带有多个表的MySQL视图导致查询缓慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我目前正在创建一个将连接多个表的视图.我可以使用左联接来获得结果，但是查询加载非常慢.我也尝试过使用内部联接，但未显示任何结果.无论如何，可以使我的查询速度更快吗? 下面是代码:
I am currently working on creating a view which will join multiple tables. I can get the result by using left join, but the query loads very slow. I have also tried using Inner Join, but not showing any result. Is there anyway to make my query fast? Below are the code :
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`%`
SQL SECURITY DEFINER
VIEW `prc`.`vsrparts_latest` AS
SELECT
`prc`.`tbsrparts_new`.`SRPartsID` AS `SRPartsID`,
`prc`.`tbsrparts_new`.`RepairID` AS `RepairID`,
`prc`.`tbsrparts_new`.`SRNo` AS `SRNo`,
`prc`.`tbsrparts_new`.`DateReceived` AS `DateReceived`,
`prc`.`tbsrparts_new`.`ShipmentDate` AS `ShipmentDate`,
`prc`.`tbparts`.`PartsNo` AS `PartsNo`,
`prc`.`tbsrparts_new`.`PartID` AS `PartID`,
`prc`.`tbparts`.`PartsDesc` AS `PartsDesc`,
`prc`.`tbcompany`.`Company` AS `Company`,
`prc`.`tbcompany`.`Alias` AS `Alias`,
`prc`.`tbsrparts_new`.`RepairCenter` AS `RepairCenter`,
`prc`.`tbsrparts_new`.`UserID` AS `UserID`,
`prc`.`tbsrparts_new`.`NinetyDaysReturn` AS `NinetyDaysReturn`,
`prc`.`tbsrparts_new`.`PartSN` AS `PartSN`,
`prc`.`tbsrparts_new`.`RefurbishedSN` AS `RefurbishedSN`,
`prc`.`tbsrparts_new`.`CountFrequency` AS `CountFrequency`,
`prc`.`tbuser`.`EmployeeID` AS `EmployeeID`,
`vpartsinfo`.`PartsNo` AS `FinalPart`,
`prc`.`tbsrparts_new`.`FinalPart` AS `FinalPartID`,
`prc`.`tbsrparts_new`.`EcoImplemented` AS `EcoImplemented`,
`prc`.`tbsrparts_new`.`VisualInspectionStatus` AS `VisualInspectionStatus`,
`prc`.`tbvisual`.`VisualInspectionErrorCode` AS `VisualInspectionErrorCode`,
`prc`.`tbvisual`.`VisualInspectionActionTaken` AS `VisualInspectionActionTaken`,
`prc`.`tbvisual`.`VisualInspectionComponentLocation` AS `VisualInspectionComponentLocation`,
`prc`.`tbsrparts_new`.`VisualInspectionResult` AS `VisualInspectionResult`,
`prc`.`tbsrparts_new`.`DebugTestingStatus` AS `DebugTestingStatus`,
`prc`.`tb1stdebug`.`FirstDebugTestingErrorCode` AS `FirstDebugTestingErrorCode`,
`prc`.`tb1stdebug`.`FirstDebugActionTaken` AS `FirstDebugActionTaken`,
`prc`.`tb1stdebug`.`FirstDebugComponentLocation` AS `FirstDebugComponentLocation`,
`prc`.`tb2nddebug`.`SecondDebugTestingErrorCode` AS `SecondDebugTestingErrorCode`,
`prc`.`tb2nddebug`.`SecondDebugActionTaken` AS `SecondDebugActionTaken`,
`prc`.`tb2nddebug`.`SecondDebugComponentLocation` AS `SecondDebugComponentLocation`,
`prc`.`tb3rddebug`.`ThirdDebugTestingErrorCode` AS `ThirdDebugTestingErrorCode`,
`prc`.`tb3rddebug`.`ThirdDebugActionTaken` AS `ThirdDebugActionTaken`,
`prc`.`tb3rddebug`.`ThirdDebugComponentLocation` AS `ThirdDebugComponentLocation`,
`prc`.`tbsrparts_new`.`DebugTestingResult` AS `DebugTestingResult`,
`prc`.`tbsrparts_new`.`FctTestingStatus` AS `FctTestingStatus`,
`prc`.`tb1stfct`.`FirstFctTestingErrorCode` AS `FirstFctTestingErrorCode`,
`prc`.`tb1stfct`.`FirstFctActionTaken` AS `FirstFctActionTaken`,
`prc`.`tb1stfct`.`FirstFctComponentLocation` AS `FirstFctComponentLocation`,
`prc`.`tb2ndfct`.`SecondFctTestingErrorCode` AS `SecondFctTestingErrorCode`,
`prc`.`tb2ndfct`.`SecondFctActionTaken` AS `SecondFctActionTaken`,
`prc`.`tb2ndfct`.`SecondFctComponentLocation` AS `SecondFctComponentLocation`,
`prc`.`tb3rdfct`.`ThirdFctTestingErrorCode` AS `ThirdFctTestingErrorCode`,
`prc`.`tb3rdfct`.`ThirdFctActionTaken` AS `ThirdFctActionTaken`,
`prc`.`tb3rdfct`.`ThirdFctComponentLocation` AS `ThirdFctComponentLocation`,
`prc`.`tbsrparts_new`.`FctTestingResult` AS `FctTestingResult`,
`prc`.`tbsrparts_new`.`RelayFailure` AS `RelayFailure`,
`prc`.`tbsrparts_new`.`RelayDateCode` AS `RelayDateCode`,
`prc`.`tbsrparts_new`.`DefectiveRelayColor` AS `DefectiveRelayColor`,
`prc`.`tbsrparts_new`.`RelayFailureLocation` AS `RelayFailureLocation`,
`prc`.`tbsrparts_new`.`DateCreated` AS `DateCreated`,
`prc`.`tbsrparts_new`.`LastUpdated` AS `LastUpdated`,
`prc`.`tbsrparts_new`.`EstimatedRepairCompletionDate` AS `EstimatedRepairCompletionDate`,
`prc`.`tbsrparts_new`.`StartRepairDate` AS `StartRepairDate`,
`prc`.`tbsrparts_new`.`Status` AS `Status`,
`prc`.`tbsrparts_new`.`AttachmentName` AS `AttachmentName`,
`prc`.`tbsrparts_new`.`PartsReturnProcess` AS `PartsReturnProcess`,
(TO_DAYS(CURDATE()) - TO_DAYS(STR_TO_DATE(REPLACE(`prc`.`tbsrparts_new`.`DateReceived`,
',',
'-'),
'%d-%M-%Y'))) AS `dateNew`
FROM
(((((((((((`prc`.`tbsrparts_new`
JOIN `prc`.`tbparts` ON ((`prc`.`tbsrparts_new`.`PartID` = `prc`.`tbparts`.`PartsID`)))
JOIN `prc`.`tbcompany` ON ((`prc`.`tbsrparts_new`.`RepairCenter` = `prc`.`tbcompany`.`CompanyID`)))
JOIN `prc`.`vpartsinfo` ON ((`vpartsinfo`.`PartsID` = `prc`.`tbsrparts_new`.`FinalPart`)))
JOIN `prc`.`tbuser` ON ((`prc`.`tbsrparts_new`.`UserID` = `prc`.`tbuser`.`UserID`)))
LEFT JOIN `prc`.`tbvisual` ON ((`prc`.`tbsrparts_new`.`RepairID` = `prc`.`tbvisual`.`RepairID`)))
LEFT JOIN `prc`.`tb1stdebug` ON ((`prc`.`tbsrparts_new`.`RepairID` = `prc`.`tb1stdebug`.`RepairID`)))
LEFT JOIN `prc`.`tb2nddebug` ON ((`prc`.`tbsrparts_new`.`RepairID` = `prc`.`tb2nddebug`.`RepairID`)))
LEFT JOIN `prc`.`tb3rddebug` ON ((`prc`.`tbsrparts_new`.`RepairID` = `prc`.`tb3rddebug`.`RepairID`)))
LEFT JOIN `prc`.`tb1stfct` ON ((`prc`.`tbsrparts_new`.`RepairID` = `prc`.`tb1stfct`.`RepairID`)))
LEFT JOIN `prc`.`tb2ndfct` ON ((`prc`.`tbsrparts_new`.`RepairID` = `prc`.`tb2ndfct`.`RepairID`)))
LEFT JOIN `prc`.`tb3rdfct` ON ((`prc`.`tbsrparts_new`.`RepairID` = `prc`.`tb3rdfct`.`RepairID`)))
ORDER BY `prc`.`tbsrparts_new`.`SRPartsID` DESC
下面是表的架构:
+-------------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------+--------------------+------+-----+---------+----------------+
| SRPartsID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(200) | NO | | NULL | |
| SRNo | varchar(200) | YES | MUL | NULL | |
| DateReceived | varchar(200) | YES | | NULL | |
| ShipmentDate | varchar(200) | YES | | NULL | |
| NinetyDaysReturn | varchar(200) | YES | | NULL | |
| PartID | varchar(200) | YES | | NULL | |
| PartSN | varchar(200) | YES | | NULL | |
| RefurbishedSN | varchar(200) | YES | | NULL | |
| FinalPart | varchar(200) | YES | | NULL | |
| DefectPart | varchar(200) | YES | | NULL | |
| RelayFailure | varchar(200) | YES | | NULL | |
| RelayDateCode | varchar(200) | YES | | NULL | |
| DefectiveRelayColor | varchar(200) | YES | | NULL | |
| RelayFailureLocation | varchar(200) | YES | | NULL | |
| RepairCenter | varchar(200) | YES | | NULL | |
| UserID | varchar(200) | YES | | NULL | |
| DateCreated | varchar(45) | YES | | NULL | |
| LastUpdated | varchar(45) | YES | | NULL | |
| LastUpdatedBy | varchar(45) | YES | | NULL | |
| EstimatedRepairCompletionDate | varchar(45) | YES | | NULL | |
| StartRepairDate | varchar(45) | YES | | NULL | |
| Attachment | longblob | YES | | NULL | |
| AttachmentName | varchar(45) | YES | | NULL | |
| AttachmentType | varchar(45) | YES | | NULL | |
| AttachmentSize | varchar(45) | YES | | NULL | |
| PartsReturnProcess | varchar(45) | YES | | NULL | |
| EcoImplemented | varchar(45) | YES | | NULL | |
| CountFrequency | varchar(45) | YES | | NULL | |
| VisualInspectionStatus | varchar(200) | YES | | NULL | |
| VisualInspectionResult | varchar(200) | YES | | NULL | |
| DebugTestingStatus | varchar(200) | YES | | NULL | |
| DebugTestingResult | varchar(200) | YES | | NULL | |
| ICTTestingStatus | varchar(200) | YES | | NULL | |
| ICTTestingResult | varchar(200) | YES | | NULL | |
| ICTTestingErrorCode | varchar(200) | YES | | NULL | |
| ICTTestingActionTaken | varchar(200) | YES | | NULL | |
| ICTTestingComponentLocation | varchar(200) | YES | | NULL | |
| ICTTestingDesignator | varchar(200) | YES | | NULL | |
| FctTestingStatus | varchar(200) | YES | | NULL | |
| FctTestingResult | varchar(200) | YES | | NULL | |
| Status | varchar(40) | YES | | NULL | |
+-------------------------------+--------------------+------+-----+---------+----------------+
tbuser
+---------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------------+------+-----+---------+----------------+
| UserID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| Username | varchar(500) | YES | | NULL | |
| Password | varchar(500) | YES | | NULL | |
| Name | varchar(500) | YES | | NULL | |
| EmployeeID | varchar(500) | YES | MUL | NULL | |
| Email | varchar(500) | YES | | NULL | |
| StatusID | "int(10) unsigned" | YES | | NULL | |
| AuthorityID | "int(10) unsigned" | YES | | NULL | |
| CompanyID | "int(10) unsigned" | YES | | NULL | |
| LastUpdated | varchar(50) | YES | | NULL | |
| LastUpdatedBy | varchar(45) | YES | | NULL | |
| LastLogin | varchar(45) | YES | | N/A | |
+---------------+--------------------+------+-----+---------+----------------+
tbparts
+-----------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------------+------+-----+---------+----------------+
| PartsID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| PartsNo | varchar(20) | NO | MUL | NULL | |
| PartsDesc | varchar(100) | YES | | NULL | |
| CompanyID | "int(10) unsigned" | NO | | NULL | |
| UserID | "int(10) unsigned" | YES | | NULL | |
| LastUpdatedTime | varchar(45) | YES | | NULL | |
| Category | varchar(45) | YES | | NULL | |
+-----------------+--------------------+------+-----+---------+----------------+
tbcompany
+--------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------------+------+-----+---------+----------------+
| CompanyID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| Company | varchar(45) | NO | MUL | NULL | |
| Alias | varchar(45) | YES | | NULL | |
| SupplierCode | varchar(45) | NO | | NULL | |
+--------------+--------------------+------+-----+---------+----------------+
vpartsinfo
+-----------------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------------+------+-----+---------+-------+
| PartsID | "int(10) unsigned" | NO | | 0 | |
| PartsNo | varchar(20) | NO | | NULL | |
| PartsDesc | varchar(100) | YES | | NULL | |
| CompanyID | "int(10) unsigned" | NO | | NULL | |
| UserID | "int(10) unsigned" | YES | | NULL | |
| LastUpdatedTime | varchar(45) | YES | | NULL | |
| Company | varchar(45) | NO | | NULL | |
| Name | varchar(500) | YES | | NULL | |
+-----------------+--------------------+------+-----+---------+-------+
tb1stdebug
+-----------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------------+------+-----+---------+----------------+
| FirstDebugID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(500) | YES | | NULL | |
| FirstDebugTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| FirstDebugActionTaken | varchar(500) | YES | | NULL | |
| FirstDebugComponentLocation | varchar(500) | YES | | NULL | |
+-----------------------------+--------------------+------+-----+---------+----------------+
tb2nddebug
+-----------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------------+------+-----+---------+----------------+
| SecondDebugID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(500) | YES | | NULL | |
| SecondDebugTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| SecondDebugActionTaken | varchar(500) | YES | | NULL | |
| SecondDebugComponentLocation | varchar(500) | YES | | NULL | |
+-----------------------------+--------------------+------+-----+---------+----------------+
tb3rddebug
+-----------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------------+------+-----+---------+----------------+
| ThirdDebugID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(500) | YES | | NULL | |
| ThirdDebugTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| ThirdDebugActionTaken | varchar(500) | YES | | NULL | |
| ThirdDebugComponentLocation | varchar(500) | YES | | NULL | |
+-----------------------------+--------------------+------+-----+---------+----------------+
tb1stfct
+---------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------------+------+-----+---------+----------------+
| FirstFctID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(45) | YES | | NULL | |
| FirstFctTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| FirstFctActionTaken | varchar(500) | YES | | NULL | |
| FirstFctComponentLocation | varchar(500) | YES | | NULL | |
+---------------------------+--------------------+------+-----+---------+----------------+
tb2ndfct
+----------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------------+------+-----+---------+----------------+
| SecondFctID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(45) | YES | | NULL | |
| SecondFctTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| SecondFctActionTaken | varchar(500) | YES | | NULL | |
| SecondFctComponentLocation | varchar(500) | YES | | NULL | |
+----------------------------+--------------------+------+-----+---------+----------------+
tb3rdfct
+---------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------------+------+-----+---------+----------------+
| ThirdFctID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(45) | YES | | NULL | |
| ThirdFctTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| ThirdFctActionTaken | varchar(500) | YES | | NULL | |
| ThirdFctComponentLocation | varchar(500) | YES | | NULL | |
+---------------------------+--------------------+------+-----+---------+----------------+
tbvisual
+-----------------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------------+--------------------+------+-----+---------+----------------+
| VisualID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(45) | YES | | NULL | |
| VisualInspectionErrorCode | varchar(500) | YES | MUL | NULL | |
| VisualInspectionActionTaken | varchar(500) | YES | | NULL | |
| VisualInspectionComponentLocation | varchar(500) | YES | | NULL | |
+-----------------------------------+--------------------+------+-----+---------+----------------+
推荐答案
(A)首先运行explain select [the whole select and join]并将输出保存在某个地方.
(A) First run explain select [the whole select and join] and save output somewhere.
(B)我计算了14个缺失的索引，这些索引在下表的键"列中表示为 MISS .尽管有表中的主键，但您正在执行表扫描，并且不使用索引.
(B) I count 14 missing indexes which I denoted as MISS in the Key column from your tables below. You are doing table scans and not using indexes despite a primary key in table.
在从"子句中，您可以在最右边看到19条否注释.但实际上总共有14个失踪事件
In your From clause you can see 19 NO comments to the far right. But in reality there are 14 total missing
(C)考虑创建所有14个丢失的键(自然会降低插入速度).
(C) Consider creating all 14 missing keys (naturally this slows down inserts).
alter table tbsrparts_new add index(RepairID);
alter table tbsrparts_new add index(PartID);
alter table tbsrparts_new add index(FinalPart);
alter table tbsrparts_new add index(DefectPart);
alter table tbsrparts_new add index(RepairCenter);
alter table tbsrparts_new add index(UserID);
alter table vpartsinfo add index(PartsID);
alter table tb1stdebug add index(RepairID);
alter table tb2nddebug add index(RepairID);
alter table tb3rddebug add index(RepairID);
alter table tb1stfct add index(RepairID);
alter table tb2ndfct add index(RepairID);
alter table tb3rdfct add index(RepairID);
alter table tbvisual add index(RepairID);
(D)运行explain select [the whole select and join]并将输出保存在某个地方.
(D) Run explain select [the whole select and join] and save output somewhere.
(E)与问题共享(A)和(D)的结果，以便有人在您需要进一步帮助时可以为您提供帮助.
(E) Share results of (A) and (D) with the Question so someone can help you if you need further help.
(F)高兴时创建您的视图.
(F) When happy create your view.
祝你好运！
SELECT
new.SRPartsID AS SRPartsID,
new.RepairID AS RepairID,
new.SRNo AS SRNo,
new.DateReceived AS DateReceived,
new.ShipmentDate AS ShipmentDate,
tbparts.PartsNo AS PartsNo,
new.PartID AS PartID,
tbparts.PartsDesc AS PartsDesc,
tbcompany.Company AS Company,
tbcompany.Alias AS Alias,
new.RepairCenter AS RepairCenter,
new.UserID AS UserID,
new.NinetyDaysReturn AS NinetyDaysReturn,
new.PartSN AS PartSN,
new.RefurbishedSN AS RefurbishedSN,
new.CountFrequency AS CountFrequency,
tbuser.EmployeeID AS EmployeeID,
vpartsinfo.PartsNo AS FinalPart,
new.FinalPart AS FinalPartID,
new.EcoImplemented AS EcoImplemented,
new.VisualInspectionStatus AS VisualInspectionStatus,
tbvisual.VisualInspectionErrorCode AS VisualInspectionErrorCode,
tbvisual.VisualInspectionActionTaken AS VisualInspectionActionTaken,
tbvisual.VisualInspectionComponentLocation AS VisualInspectionComponentLocation,
new.VisualInspectionResult AS VisualInspectionResult,
new.DebugTestingStatus AS DebugTestingStatus,
prc.tb1stdebug.FirstDebugTestingErrorCode AS FirstDebugTestingErrorCode,
prc.tb1stdebug.FirstDebugActionTaken AS FirstDebugActionTaken,
prc.tb1stdebug.FirstDebugComponentLocation AS FirstDebugComponentLocation,
prc.tb2nddebug.SecondDebugTestingErrorCode AS SecondDebugTestingErrorCode,
prc.tb2nddebug.SecondDebugActionTaken AS SecondDebugActionTaken,
prc.tb2nddebug.SecondDebugComponentLocation AS SecondDebugComponentLocation,
prc.tb3rddebug.ThirdDebugTestingErrorCode AS ThirdDebugTestingErrorCode,
prc.tb3rddebug.ThirdDebugActionTaken AS ThirdDebugActionTaken,
prc.tb3rddebug.ThirdDebugComponentLocation AS ThirdDebugComponentLocation,
new.DebugTestingResult AS DebugTestingResult,
new.FctTestingStatus AS FctTestingStatus,
tb1stfct.FirstFctTestingErrorCode AS FirstFctTestingErrorCode,
tb1stfct.FirstFctActionTaken AS FirstFctActionTaken,
tb1stfct.FirstFctComponentLocation AS FirstFctComponentLocation,
tb2ndfct.SecondFctTestingErrorCode AS SecondFctTestingErrorCode,
tb2ndfct.SecondFctActionTaken AS SecondFctActionTaken,
tb2ndfct.SecondFctComponentLocation AS SecondFctComponentLocation,
tb3rdfct.ThirdFctTestingErrorCode AS ThirdFctTestingErrorCode,
tb3rdfct.ThirdFctActionTaken AS ThirdFctActionTaken,
tb3rdfct.ThirdFctComponentLocation AS ThirdFctComponentLocation,
new.FctTestingResult AS FctTestingResult,
new.RelayFailure AS RelayFailure,
new.RelayDateCode AS RelayDateCode,
new.DefectiveRelayColor AS DefectiveRelayColor,
new.RelayFailureLocation AS RelayFailureLocation,
new.DateCreated AS DateCreated,
new.LastUpdated AS LastUpdated,
new.EstimatedRepairCompletionDate AS EstimatedRepairCompletionDate,
new.StartRepairDate AS StartRepairDate,
new.Status AS Status,
new.AttachmentName AS AttachmentName,
new.PartsReturnProcess AS PartsReturnProcess,
(TO_DAYS(CURDATE()) - TO_DAYS(STR_TO_DATE(REPLACE(new.DateReceived,
',',
'-'),
'%d-%M-%Y'))) AS dateNew
FROM
tbsrparts_new new
JOIN tbparts ON tbsrparts_new.PartID=tbparts.PartsId -- indexes there: **NO**, Yes
JOIN tbcompany ON tbsrparts_new.RepairCenter = tbcompany.CompanyID -- indexes there: **NO** , Yes
JOIN vpartsinfo ON vpartsinfo.PartsID = new.FinalPart -- indexes there: **NO**, **NO**
JOIN tbuser ON new.UserID = tbuser.UserID -- indexes there: **NO**, Yes
LEFT JOIN tbvisual ON new.RepairID = tbvisual.RepairID -- indexes there: **NO**, **NO**
LEFT JOIN tb1stdebug ON new.RepairID = tb1stdebug.RepairID -- indexes there: **NO**, **NO**
LEFT JOIN tb2nddebug ON new.RepairID = tb2nddebug.RepairID -- indexes there: **NO**, **NO**
LEFT JOIN tb3rddebug ON new.RepairID = tb3rddebug.RepairID -- indexes there: **NO**, **NO**
LEFT JOIN tb1stfct ON new.RepairID = tb1stfct.RepairID -- indexes there: **NO**, **NO**
LEFT JOIN tb2ndfct ON new.RepairID = tb2ndfct.RepairID -- indexes there: **NO**, **NO**
LEFT JOIN tb3rdfct ON new.RepairID = tb3rdfct.RepairID -- indexes there: **NO**, **NO**
ORDER BY new.SRPartsID DESC
下面是相关表的架构.在答案的开头记下我的评论(B):
Below is schema of related tables. Note my comment (B) at beginning of answer:
+-------------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------+--------------------+------+-----+---------+----------------+
| SRPartsID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(200) | NO |MISS | NULL | |
| SRNo | varchar(200) | YES | MUL | NULL | |
| DateReceived | varchar(200) | YES | | NULL | |
| ShipmentDate | varchar(200) | YES | | NULL | |
| NinetyDaysReturn | varchar(200) | YES | | NULL | |
| PartID | varchar(200) | YES |MISS | NULL | |
| PartSN | varchar(200) | YES | | NULL | |
| RefurbishedSN | varchar(200) | YES | | NULL | |
| FinalPart | varchar(200) | YES |MISS | NULL | |
| DefectPart | varchar(200) | YES |MISS | NULL | |
| RelayFailure | varchar(200) | YES | | NULL | |
| RelayDateCode | varchar(200) | YES | | NULL | |
| DefectiveRelayColor | varchar(200) | YES | | NULL | |
| RelayFailureLocation | varchar(200) | YES | | NULL | |
| RepairCenter | varchar(200) | YES |MISS | NULL | |
| UserID | varchar(200) | YES |MISS | NULL | |
| DateCreated | varchar(45) | YES | | NULL | |
| LastUpdated | varchar(45) | YES | | NULL | |
| LastUpdatedBy | varchar(45) | YES | | NULL | |
| EstimatedRepairCompletionDate | varchar(45) | YES | | NULL | |
| StartRepairDate | varchar(45) | YES | | NULL | |
| Attachment | longblob | YES | | NULL | |
| AttachmentName | varchar(45) | YES | | NULL | |
| AttachmentType | varchar(45) | YES | | NULL | |
| AttachmentSize | varchar(45) | YES | | NULL | |
| PartsReturnProcess | varchar(45) | YES | | NULL | |
| EcoImplemented | varchar(45) | YES | | NULL | |
| CountFrequency | varchar(45) | YES | | NULL | |
| VisualInspectionStatus | varchar(200) | YES | | NULL | |
| VisualInspectionResult | varchar(200) | YES | | NULL | |
| DebugTestingStatus | varchar(200) | YES | | NULL | |
| DebugTestingResult | varchar(200) | YES | | NULL | |
| ICTTestingStatus | varchar(200) | YES | | NULL | |
| ICTTestingResult | varchar(200) | YES | | NULL | |
| ICTTestingErrorCode | varchar(200) | YES | | NULL | |
| ICTTestingActionTaken | varchar(200) | YES | | NULL | |
| ICTTestingComponentLocation | varchar(200) | YES | | NULL | |
| ICTTestingDesignator | varchar(200) | YES | | NULL | |
| FctTestingStatus | varchar(200) | YES | | NULL | |
| FctTestingResult | varchar(200) | YES | | NULL | |
| Status | varchar(40) | YES | | NULL | |
+-------------------------------+--------------------+------+-----+---------+----------------+
tbuser
+---------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------------+------+-----+---------+----------------+
| UserID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| Username | varchar(500) | YES | | NULL | |
| Password | varchar(500) | YES | | NULL | |
| Name | varchar(500) | YES | | NULL | |
| EmployeeID | varchar(500) | YES | MUL | NULL | |
| Email | varchar(500) | YES | | NULL | |
| StatusID | "int(10) unsigned" | YES | | NULL | |
| AuthorityID | "int(10) unsigned" | YES | | NULL | |
| CompanyID | "int(10) unsigned" | YES | | NULL | |
| LastUpdated | varchar(50) | YES | | NULL | |
| LastUpdatedBy | varchar(45) | YES | | NULL | |
| LastLogin | varchar(45) | YES | | N/A | |
+---------------+--------------------+------+-----+---------+----------------+
tbparts
+-----------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------------+------+-----+---------+----------------+
| PartsID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| PartsNo | varchar(20) | NO | MUL | NULL | |
| PartsDesc | varchar(100) | YES | | NULL | |
| CompanyID | "int(10) unsigned" | NO | | NULL | |
| UserID | "int(10) unsigned" | YES | | NULL | |
| LastUpdatedTime | varchar(45) | YES | | NULL | |
| Category | varchar(45) | YES | | NULL | |
+-----------------+--------------------+------+-----+---------+----------------+
tbcompany
+--------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------------+------+-----+---------+----------------+
| CompanyID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| Company | varchar(45) | NO | MUL | NULL | |
| Alias | varchar(45) | YES | | NULL | |
| SupplierCode | varchar(45) | NO | | NULL | |
+--------------+--------------------+------+-----+---------+----------------+
vpartsinfo
+-----------------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------------+------+-----+---------+-------+
| PartsID | "int(10) unsigned" | NO |MISS | 0 | |
| PartsNo | varchar(20) | NO | | NULL | |
| PartsDesc | varchar(100) | YES | | NULL | |
| CompanyID | "int(10) unsigned" | NO | | NULL | |
| UserID | "int(10) unsigned" | YES | | NULL | |
| LastUpdatedTime | varchar(45) | YES | | NULL | |
| Company | varchar(45) | NO | | NULL | |
| Name | varchar(500) | YES | | NULL | |
+-----------------+--------------------+------+-----+---------+-------+
tb1stdebug
+-----------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------------+------+-----+---------+----------------+
| FirstDebugID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(500) | YES |MISS | NULL | |
| FirstDebugTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| FirstDebugActionTaken | varchar(500) | YES | | NULL | |
| FirstDebugComponentLocation | varchar(500) | YES | | NULL | |
+-----------------------------+--------------------+------+-----+---------+----------------+
tb2nddebug
+-----------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------------+------+-----+---------+----------------+
| SecondDebugID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(500) | YES |MISS | NULL | |
| SecondDebugTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| SecondDebugActionTaken | varchar(500) | YES | | NULL | |
| SecondDebugComponentLocation | varchar(500) | YES | | NULL | |
+-----------------------------+--------------------+------+-----+---------+----------------+
tb3rddebug
+-----------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------------+------+-----+---------+----------------+
| ThirdDebugID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(500) | YES |MISS | NULL | |
| ThirdDebugTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| ThirdDebugActionTaken | varchar(500) | YES | | NULL | |
| ThirdDebugComponentLocation | varchar(500) | YES | | NULL | |
+-----------------------------+--------------------+------+-----+---------+----------------+
tb1stfct
+---------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------------+------+-----+---------+----------------+
| FirstFctID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(45) | YES |MISS | NULL | |
| FirstFctTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| FirstFctActionTaken | varchar(500) | YES | | NULL | |
| FirstFctComponentLocation | varchar(500) | YES | | NULL | |
+---------------------------+--------------------+------+-----+---------+----------------+
tb2ndfct
+----------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------------+------+-----+---------+----------------+
| SecondFctID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(45) | YES |MISS | NULL | |
| SecondFctTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| SecondFctActionTaken | varchar(500) | YES | | NULL | |
| SecondFctComponentLocation | varchar(500) | YES | | NULL | |
+----------------------------+--------------------+------+-----+---------+----------------+
tb3rdfct
+---------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------------+------+-----+---------+----------------+
| ThirdFctID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(45) | YES |MISS | NULL | |
| ThirdFctTestingErrorCode | varchar(500) | YES | MUL | NULL | |
| ThirdFctActionTaken | varchar(500) | YES | | NULL | |
| ThirdFctComponentLocation | varchar(500) | YES | | NULL | |
+---------------------------+--------------------+------+-----+---------+----------------+
tbvisual
+-----------------------------------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------------+--------------------+------+-----+---------+----------------+
| VisualID | "int(10) unsigned" | NO | PRI | NULL | auto_increment |
| RepairID | varchar(45) | YES |MISS | NULL | |
| VisualInspectionErrorCode | varchar(500) | YES | MUL | NULL | |
| VisualInspectionActionTaken | varchar(500) | YES | | NULL | |
| VisualInspectionComponentLocation | varchar(500) | YES | | NULL | |
+-----------------------------------+--------------------+------+-----+---------+----------------+
作为尝试解决似乎与索引优化无关的过多数据的附带问题:
As a side issue attempting to solve excess data appearing unrelated to index optimization:
A)输出
+----------------------------+-----------------------------+----------------------------+
| FirstDebugTestingErrorCode | SecondDebugTestingErrorCode | ThirdDebugTestingErrorCode |
+----------------------------+-----------------------------+----------------------------+
| T00111 | T03333 | T05555 |
| T00111 | T03333 | T06666 |
| T00111 | T04444 | T05555 |
| T00111 | T04444 | T06666 |
| T02222 | T03333 | T05555 |
| T02222 | T03333 | T06666 |
| T02222 | T04444 | T05555 |
+----------------------------+-----------------------------+----------------------------+
B)所需的输出
+----------------------------+-----------------------------+----------------------------+
| FirstDebugTestingErrorCode | SecondDebugTestingErrorCode | ThirdDebugTestingErrorCode |
+----------------------------+-----------------------------+----------------------------+
| T00111 | T04444 | T06666 |
| T02222 | T03333 | T05555 |
+----------------------------+-----------------------------+----------------------------+
这篇关于带有多个表的MySQL视图导致查询缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
