In large projects, relational database multi-table joint queries are frequent. Currently, the project has the following seven tables, each of which expresses a small amount of data, but the seven tables are queried through multi-table joint queries.
In large projects, relational database multi-table joint queries are frequent. Currently, the project has the following seven tables, each of which expresses a small amount of data, but the seven tables are queried through multi-table joint queries.
Abstract: In large projects, relational database multi-table joint queries are frequent. Currently, the following seven tables are available in the project, and each table shows a small amount of data, however, the query speed of the seven tables through multi-table joint query is very slow, hoping to provide an SQL statement with a high query efficiency.
Table 1: Dimensions, field id, operkey, and so on (3500 data volume)
Table 2: POLICY, field id, name, and so on (5000 data volume)
Table 3: STRATEGY, field id, name, and so on (5000 data volume)
Table 4: STRATEGY_APPROVER, field id, userid, and approver (50000 data volume)
Table 5: POLICY_OPER (intermediate table of table 1 and table 2), field policyid, operid
Table 6: STRATEGY_POLICY (intermediate table in table 2 and table 3), field policyid, strategyid
Table 7: STRATEGY_APPROVER_RELA (intermediate table in table 3 and table 4), field strategyid, strategyapproverid
The query statement used on the project is as follows:
Select * from STRATEGY where st. id in (
Select * from STRATEGY st
Inner join STRATEGY_POLICY sp on st. id = sp. strategyid
Inner join POLICY p on p. id = sp. policyid
Inner join policy_policpo on po. policyid = p. id
Inner join into o. id = po. operid
Inner join STRATEGY_APPROVER_RELA sar on sar. strategyid = st. id
Inner join STRATEGY_APPROVER sa on sa. id = sar. strategyid
Where st. id =? And st. name =? And... and
)
Note: The above query efficiency and its slowness have exceeded the difference of user experience, hoping to provide SQL statements with faster query efficiency;