select XXXXXXXXXXXXXXXXXXXXXXXXXX
FROM FlowFile,FlowStep,WorkFlow
WHERE FlowFile.FlowFlag=0
and FlowFile.FlowId=141
and FlowFile.FlowStepID=FlowStep.StepID
and FlowFile.FlowID =workflow.FlowID
AND exists (
select 1 from flowtype
where flowtypeid=flowfile.flowtypeid
)
ORDER BY FlowFile.FileID desc如上sql,如何优化,要是创建索引的话,怎么创建比较合适
各表记录如下
FlowFile, 约 20w,随时间增加
FlowStep, 约 3000 很少增加
WorkFlow 约 200 很少增加
FROM FlowFile,FlowStep,WorkFlow
WHERE FlowFile.FlowFlag=0
and FlowFile.FlowId=141
and FlowFile.FlowStepID=FlowStep.StepID
and FlowFile.FlowID =workflow.FlowID
AND exists (
select 1 from flowtype
where flowtypeid=flowfile.flowtypeid
)
ORDER BY FlowFile.FileID desc如上sql,如何优化,要是创建索引的话,怎么创建比较合适
各表记录如下
FlowFile, 约 20w,随时间增加
FlowStep, 约 3000 很少增加
WorkFlow 约 200 很少增加
FlowStep StepID建索引
workflow FlowID建索引
flowtype flowtypeid建索引
FlowStep、workflow、flowtype可以考虑做索引组织表(IOT)
and FlowFile.FlowId=141在这两个字段上按须序建立组合索引
再来看看查询计划与以前的有何改变
workflow FlowID建索引 flowtype flowtypeid建索引