> just moving sorting to the frontend took a 3 second query down to 10ms
I've seen this too, but the problem can often be optimized in SQL as well. For instance, if you expect that only one User will match a specific username, then adding LIMIT 1 to the query will instruct the DB to return after finding a single hit, which can significantly shorten query time. But then adding an ORDER BY clause will cause (at least MySQL) to parse the entire table anyway, even though you've got the LIMIT clause. This is just an example, one really needs to know their database and their application to make these types of decisions.
But the premature optimization applies to SQL as well, if the results can be sorted/filtered/transformed in application memory better than in the database so be it.
I've seen this too, but the problem can often be optimized in SQL as well. For instance, if you expect that only one User will match a specific username, then adding LIMIT 1 to the query will instruct the DB to return after finding a single hit, which can significantly shorten query time. But then adding an ORDER BY clause will cause (at least MySQL) to parse the entire table anyway, even though you've got the LIMIT clause. This is just an example, one really needs to know their database and their application to make these types of decisions.
But the premature optimization applies to SQL as well, if the results can be sorted/filtered/transformed in application memory better than in the database so be it.