Why are my queries taking so long? [closed]

Question

In order to quickly calculate some features for later calculations I have attempted to store a table that allows looking up distances between locations that have been pre-calculated. I am using Python and SQLalchemy. I have 76,807,696 rows in my table. However a single query is taking upwards of 300 seconds.

My Database is defined as:

class LocationsLookupTable(db.Model):
    """Use this class to create the database Model for the locations lookup 
table.
This class is used for creating the database table Model for the locations
lookup table. It will store the ID pair and their distance value. There
will also be a unique ID for each value in the database table.
"""

id = db.Column(db.Integer, primary_key=True, unique=True)
firstId = db.Column(db.Integer, nullable=False)
secondId = db.Column(db.Integer, nullable=False)
firstLocationGeoPointX = db.Column(db.Float)
firstLocationGeoPointY = db.Column(db.Float)
secondLocationGeoPointX = db.Column(db.Float)
secondLocationGeoPointY = db.Column(db.Float)
distanceValue = db.Column(db.Integer)

And I am querying it using the following query:

result = models.LocationsLookupTable.query.filter_by(
            firstId=firstId, secondId=secondId).first()

I have some theories as to why the performance is so bad. The first is that I simply have too many rows. The second is that a database is not suited for this task. The third is that my query is not well formulated. This is however my first attempt at using a Database of any kind so I would like to ask how I can speed up my query before I would make any large changes to my project.

Edit: I solved my problem. @ypercubeᵀᴹ was indeed correct in that the solution was to index my columns that I was querying. SELECT statements are incredibly fast now by a huge margin, taking roughly 0.03 seconds. Thankyou.

Answer 1

I have some theories as to why the performance is so bad.

Lets see.

The first is that I simply have too many rows.

Probably not. There is no limit on the number of rows in a (SQL) DBMS. 80 million rows is not that much these days.

The second is that a database is not suited for this task.

Unlikely. We don't know which DBMS you use (Postgres? MySQL? SQLite? SQL Server? There are hundreds of them) but I think any of them could handle what seems to be a simple query.

The third is that my query is not well formulated.

No, it seems good. The query produced will look like:

SELECT id, firstid, secondid, ..., distanceValue
FROM LocationsLookupTable
WHERE firstId = ?
  AND secondId = ? ;

So, what is causing this?

If that is all the code that is creating the table, then what is missing is indexes. Lacking them, the database optimizer/planner has to create a query plan that scans the entire table. While the table is not big, it is not very small either.

Adding an index on (firstid, secondid) will improve performance a lot.

However 300 seconds looks too long, even for a table scan, so the hardware or the DBMS settings should be examined, too.