认为我们没有达到两个表
表1:路线
Id | factory_port_id | port_of_loading | port_of_destination |
---|---|---|---|
1 | 1 | 2 | 3 |
2 | 3 | 1 | 2 |
3 | 3 | 1 | 2 |
表2:港口
Id | port_name |
---|---|
1 | mundra |
2 | kolkata |
3 | chennai |
现在要向用户展示路线,我们需要在问询后加入表格。
SELECT
r.Id,
f.port_name AS factory_port_name,
pl.port_name AS port_of_loading_name,
pd.port_name AS port_of_destination_name
FROM
Routes AS r
JOIN
ports AS f ON r.factory_port_id = f.Id
JOIN
ports AS pl ON r.port_of_loading = pl.Id
JOIN
ports AS pd ON r.port_of_destination = pd.Id;
指数已列在表2的所有栏目中:
CREATE INDEX idx_port_name ON ports (port_name);
CREATE INDEX idx_factory_port_id ON Routes (factory_port_id);
CREATE INDEX idx_factory_port_id_1 ON Routes (port_of_loading);
CREATE INDEX idx_factory_port_id_2 ON Routes (port_of_destination);
Question How to make the above query performant as I need to filter at least 10 million rows in the route table and 1 million rows in the port tables.
我认为,路线表不通,避免加入。 这一良好想法吗?
是否有任何其他解决办法? 谁能帮助我找到一个? 我在谷歌上找不到任何东西。