I want to find all the shortest routes in an unweighted graphic stored in a SQL database. The graph has around 460,000,000 edges and 5,600,000 nodes. My approach is to use a bidirectional BFS to find all the shortest routes.

I implemented the algorithm in the following way: a search starts from the source node to the destination node and another one starts at the destination node and searches in the direction of the source. If there is an intersection between the nodes visited by the BFS from the origin to the destination and the BFS from the destination to the origin, a route has been found. As I wish to find all the routes, the current distance from the source is saved and the current distance from the target is saved. The search continues as long as there are more nodes with the same distance from the source or the same distance from the target. Now all the roads have been found, but not only the short circuits, but also once again. Therefore, the last step is to eliminate the routes that are not the shortest.

The SQL database looks like this:

```
CREATE CHANNEL TABLES (
edge_from UNSIGNED int (10) NOT NULL,
edge_target UNSIGNED int (10) NOT NULL
);
CREATE INDEX edges_from_index ON edge (edge_from);
CREATE INDEX edges_target_index ON edge (edge_target);
```

The execution of the function on my computer takes a few seconds, even if the route is quite short. A quick look at the time spent on each function with `cProfile`

reveals that the search for SQL takes longer. Is there anything I can do to improve the complexity of time and therefore decrease searches or can I improve my SQL database / SQL query to make it faster? I also appreciate any comments about my code in general. Here is my code:

```
import sqlite3
import collections
import itertools
def bidirectional_BFS (connection, source, destination):
""
Returns all the shortest routes between & # 39; origin & # 39; and & # 39; destiny & # 39;
""
source_queue = collections.deque ((source,))
target_queue = collections.deque ((target,))
source_visited = {source: 0} # assign a node to its distance from the source
target_visited = {target: 0} # assign a node at its distance from the target
# Track all the ways to reach a particular node
source_parent = collections.defaultdict (set)
target_parent = collections.defaultdict (set)
fuente_parente[source].add (None)
target_parent[target].add (None)
found_path = False
source_deapth = 0
target_deapth = 0
# The set of all intersections between the two searches.
intersections = set ()
while source_queue and target_queue:
if found_path and (source_visited[source_queue[0]]> source_deapth and target_visited[target_queue[0]]> target_deapth):
# We're done. All nodes in the current deapth have been scanned
intersections = filter_intersections (source_visited, target_visited, intersections)
return construct_path (source_parent, target_parent, source, target, intersections)
if found_path and source_visited[source_queue[0]]> source_deapth:
# Found a route but the destination BFS still has more nodes to explore
target_added, t_deapth = BFS_target (target_queue, target_visited, target_parent, connection)
intersections | = target_added & source_visited.keys ()
elif found_path and target_visited[target_queue[0]]> target_deapth:
# Found a route but the source BFS still has more nodes to explore
source_added, s_deapth = BFS_source (source_queue, source_visited, source_parent, connection)
intersections | = source_added & target_visited.keys ()
plus:
source_added, s_deapth = BFS_source (source_queue, source_visited, source_parent, connection)
target_added, t_deapth = BFS_target (target_queue, target_visited, target_parent, connection)
intersections | = source_added & target_visited.keys ()
intersections | = target_added & source_visited.keys ()
If no route and intersections are found:
# We find a route, so we search the search deapth to the current deapth.
found_path = True
source_deapth = s_deapth
target_deapth = t_deapth
if found_path:
return construct_path (source_parent, target_parent, source, target, intersections)
plus:
return none
def filter_intersections (source_visited, target_visited, intersections):
""
Returns only intersections where the combined distance of the source
to the intersection and from the goal to the intersection are the smallest
""
filterd = set ()
shorter = float (& # 39; inf & # 39;)
For intersection intersections:
if source_visited[intersection] + target_visited[intersection] <the shortest:
shortest = source_visited[intersection] + target_visited[intersection]
filterd = {intersection}
elif source_visited[intersection] + target_visited[intersection] == shorter:
filterd.add (intersection)
return filter
def construct_path (source_parent, target_parent, source, target, intersections):
""
Build all the routes and return a list of lists where each list is a route
""
paths = set ()
For intersection intersections:
from_source_to_inter = construct_path_from_to (source_parent, source, intersection)
from_inter_to_target = construct_path_from_to (target_parent, target, intersection, reverse = True)
for the path in itertools.product (from_source_to_inter, from_inter_to_target):
paths.add (tuple (path[0] + road[1][1:]))
return paths
def construct_path_from_to (source_parent, target, start, reverse = False):
""
Build all the paths between the start and the destination recursively. If the opposite is true
Then all roads are reversed.
""
if start == goal:
he came back
The[[[target]]roads = []
for parents at source_parent[start]:
for the path in construct_path_from_to (source_parent, target, parent, reverse):
if it is reversed:
path.insert (0, start)
plus:
path.append (start)
paths.append (path)
return paths
def BFS_source (queue, visited, parent, connection):
""
Execute an iteration of the BFS from the origin to the destination and then return
The edges scanned during the iteration and the current detail of the search.
""
current = queue.popleft ()
added = set ()
for (neighbor,) in connection.execute (& # 39; SELECT edge_target FROM the edges WHERE edge_from =?; & # 39 ;, (current,)):
If the neighbor has not visited:
father[neighbor].add (current)
visited[neighbor] = visited[current] + 1
queue.append (neighbor)
added.add (neighbor)
Elif visited[current] + 1 == visited[neighbor]:
father[neighbor].add (current)
aggregate return, visited[current]
def BFS_target (queue, visited, parent, connection):
""
Performs an iteration of the BFS from destination to origin and then returns
The edges scanned during the iteration and the current detail of the search.
""
current = queue.popleft ()
added = set ()
for (neighbor,) in connection. run (& # 39; SELECT edge_from FROM edges WHERE edge_target =?; & # 39 ;, (current,)):
If neighbornot in visited:
father[neighbor].add (current)
visited[neighbor] = visited[current] + 1
queue.append (neighbor)
added.add (neighbor)
Elif visited[current] + 1 == visited[neighbor]:
father[neighbor].add (current)
aggregate return, visited[current]
```