emotionull emotionull - 7 months ago 22
SQL Question

SQL Table Structure for frequent greater than and less than queries

I have to implement a table to frequently query for 'less than' and 'greater than' on 2 columns (xco, yco). For example, consider the following table

enter image description here

I would frequently fire a query like - give me all ids that have xco > 6. Or give me all ids that have yco < 9. xco and yco would change. But not so frequently.

What could be a good database design for such use case? I am expecting the number of rows to be between 10,000 to 50,000. I am currently trying AWS RDS since other parts of the system are on AWS. Would DynamoDB be any good for this case? I have pretty basic knowledge about SQL and almost none about NoSQL.

Answer

Any relational database will easily perform such queries.

CREATE TABLE foo (
    id ...
    xco INT ...
    yco INT ...
    PRIMARY KEY(id),
    INDEX(xco),
    INDEX(yco)
)

Then these can easily be done:

SELECT * FROM foo WHERE xco > 6;
SELECT * FROM foo WHERE yco < 9;
Comments