CODEWITHSUNDEEP
mysqlnode.jsapiapi-design
Rafael Veiga
Rafael Veiga

Reputation: 33

How to handle result filtering on a REST API endpoint?

I have a Node.js REST API, working directly with a MySQL database via node-mysql.

I'm looking for a way to filter the database results on an endpoint in the most performatic way.

Currently, I have an endpoint /api/v2/matches, where my API code queries the database and outputs the results as a JSON response.

I'm planning to use filters on this endpoint. e.g. /api/v2/matches?filter[team]=TeamId

What is the best way/most performatic way to filter those database results in my code?

Should I:

Please keep in mind that my current query for this endpoint (the one that returns all results) has a few INNER JOINS going on. I'm using LIMIT 50 on this query as well.

Upvotes: 2

Views: 1129

Answers (1)

Peter G
Peter G

Reputation: 2821

Disk access time and data transfer are the main bottlenecks here. However, the LIMIT 50 has an interesting implication on this. You should use the filters in the DB query since it will limit the amount of time the DB spends searching and it will also limit the amount of data transferred if your DB is remote (if your DB is not remote, the gains will be smaller but still there). The slight overhead incurred by building the query is negligible compared to this.

SELECT * queries are certainly designed for speed, but your DBMS will have to cover the entire dataset. You can get a better execution time with SELECT WHERE especially if you provide the appropriate index(es) in your DB schema since you're limited to 50 results, even with JOINs.

Also, keep in mind that SELECT WHERE queries have years upon years of research behind them and tricks to speed them up, whereas Array.prototype.filter is likely a very simple implementation of "if (lambda(x)) arr.append(x)" or something like that. There's a good reason pretty much every company out there uses it instead of filter.

Upvotes: 1

Related Questions