All geek questions in one place

Node connection request optimization

I have a database of nodes and methods. The method comprises two or more nodes. Some nodes belong to several paths and are therefore called a “connection” between two or more methods.

I am trying to find all nodes that combine two or more ways. So I use this query,

SELECT * 
FROM way_nodes wl 
JOIN way_nodes wr 
ON wr.node_id = wl.node_id AND wr.way_id != wl.way_id

The way_nodes table contains a list of nodes in its own way.

However, it terribly slows down my database using 9,021 paths and 43,706 nodes and gives me only 20-30 nodes per second.

Initially, I tried to count the number of times a node was used, but it still takes a lot of time.

I am using SQLite3, but I suspect that my problem applies to all databases. How to optimize such a request?

+3
2

, , , node - :

SELECT node_id, count(distinct way_id)
FROM way_nodes
GROUP BY node_id
HAVING count(distinct way_id) > 1
+1

?

+1

Source: https://habr.com/ru/post/1772558/

More articles:


All Articles