4 ms·

+1 for sql. I once was looking into a slow query and had to know that ordering text field in mysql requires a filesort regardless of how much data was involved.

+1 for sql. I once was looking into a slow query and had to know that ordering text field in mysql requires a filesort regardless of how much data was involved. which means touching a disk. Was easy enough to make a schema change to a varchar small enough not to also trigger a filesort. But learning that required several hours of scouring documentation and making sure the schema change wasn’t modifying the requirements significantly. Also what’s fun is that the data in the tables can change the explain plan. I got to see this myself when dealing with dev vs prod data.

necovek 6y ago
Knowing the basics of CS would surely help in set the expectation for such "SQL" (or rather, RDBMS) behaviour: the problem is the problem of sorting a very large array of strings, which is unsurprisingly a hard one. The very basic experience with RDBMS would point you at a better solution: indexes. Not sure about MySQL or standard SQL, but with Postgres you can keep an ordered index that will be used in an ORDER BY query. It's a common trick employed in programming in general: trade the update/insert cost for query cost (indexes take time to update, but querying is then fast).
Tomis02 6y ago
> Also what’s fun is that the data in the tables can change the explain plan. 1. This change tends to be in your favour, not against you. 2. Data is important, not the code. Of course the plan would change if your data changes. That is the point of the query planner, to find the most efficient way of retrieving the data, based of course on the contents of the database. I agree that you need a good knowledge of your database in order to be effective. Just like with C++, for example. Unfortunately we're not at the point where we can achieve optimal results without understanding the fundamentals, regardless of the programming paradigm.

necovek 6y ago

Knowing the basics of CS would surely help in set the expectation for such "SQL" (or rather, RDBMS) behaviour: the problem is the problem of sorting a very large array of strings, which is unsurprisingly a hard one. The very basic experience with RDBMS would point you at a better solution: indexes. Not sure about MySQL or standard SQL, but with Postgres you can keep an ordered index that will be used in an ORDER BY query. It's a common trick employed in programming in general: trade the update/insert cost for query cost (indexes take time to update, but querying is then fast).

Tomis02 6y ago

> Also what’s fun is that the data in the tables can change the explain plan. 1. This change tends to be in your favour, not against you. 2. Data is important, not the code. Of course the plan would change if your data changes. That is the point of the query planner, to find the most efficient way of retrieving the data, based of course on the contents of the database. I agree that you need a good knowledge of your database in order to be effective. Just like with C++, for example. Unfortunately we're not at the point where we can achieve optimal results without understanding the fundamentals, regardless of the programming paradigm.