It all depends on the query types, but broadly speaking...
If world is very large, as in an MMO, use a database, whether SQL or noSQL style is up to you, but it is a lot simpler to read (with your own eyes) the output of noSQL DBs which serve JSON output natively (GraphQL, MongoDB). Database tech is designed by specialists to cope admirably with large quantities of data, i.e. in the hundreds of thousands to millions of entries per table.
Even though the database may get millions of results very quickly once it receives that query, latency to send query and retrieve results can impact performance due to interprocess communication overhead. So you'd ideally queries out as soon as possible, at end of last frame.
A database's fixed interface can make it easier for secondary systems to operate against, e.g. level editors, without being broken by breaking changes to your game's core. On a secure remote server, it can also keep things safe from player's prying eyes / fingers.
Persistence these days can be achieved with something as simple as LocalStorage
in Unity or HTML5, so this is not a reason in itself to use a DB.
Ordinarily in games, we'd split entities up into some kind of spatial bucketing system. For example, if we have a 2D grid, then we'd sort entities each frame into grid cell buckets, and then when checking entities against each other, we'd only compare against entities in the same cell or one of its eight neighbours. Dimensions of each cell (typically the same across 2 or 3 dimensions) depend on the maximum distance across wish you which to compare entities in your queries.
To create the grid (I assume C# as you have not specified):
const int X = 0;
const int Y = 0;
const int CELLWIDTH = 32;
var grid = new GridCell[,] //x, y or y, x
for (int y = 0; y < grid.GetLength(X); y++)
for (int x = 0; x < grid.GetLength(Y); x++)
grid[x, y] = new GridCell(cellWidth, etc);
//GridCell has an empty array of entities created in its constructor.
//Instead of GridCell here, can even just use a 1D array of entity indices.
Sort entities into these, at end of each frame or start of next frame like
//clear each GridCell's .entities first! left to the reader
//fresh sort into grid cells
foreach (Entity entity in world.entities)
{
int xGrid = entity.pos.x / CELLWIDTH;
int yGrid = entity.pos.y / CELLWIDTH;
grid[xGrid, yGrid].entities.push(entity);
}
You now have all your entities sorted spatially, and can use as
GridCell cell = grid[entity.pos.x, entity.pos.y];
//...now you can consider all other entities in this cell
//or even use some simple arithmetic on x and y to get all GridCell neighbours, too...
GridCell[] cells = grid.getCellNeighbourhoodAround(entity.pos); //this cell + all 8 neighbours
Use LINQ
If you are using C#, LINQ can do this very efficiently both in terms of code brevity and performance:
var results = from entity in gridCell.entities
where (entity.pos - other.pos).magnitude < 10 //distance check
select entity.Key; //returns a list of IDs
Rather than using gridCell.entities
, you could query across all entities in a neighbourhood of 3x3 cells for instance, by assembling each .entities
into a single, larger array to be queried, or you could query the whole world using the complete (non-bucketised) world.entities
.
Conclusion
In games, a database usually won't replace the core data structures. It typically acts in addition to those, and only where required. This is out of necessity, for performance reasons.