5 ms·

> Not true. If you have single column indexes on both name and email, it could use the two indexes, though not as efficient as a single two-column index. If you

by tibbar 1y ago

> Not true. If you have single column indexes on both name and email, it could use the two indexes, though not as efficient as a single two-column index. If you query "name plus email" and the index is "name, email, age" then it could use the index. See, there are databases that implement clever optimizations like this, but those are going to vary widely by database and you would need some domain expertise with that system to know if such optimizations are working. By contrast, this mental model does help you ensure that you can create indexes that are actually helpful in the vast majority of databases. So I think the author's mental model is working out pretty well for him here, honestly.

rileymat2 1y ago
You have me curious which databases would not make this optimization, it seems like table stakes?
- tibbar 1y ago
  For example, many NoSQL databases are less sophisticated in query planning.
blindriver 1y ago
The point is that there's misinformation in the things that he's saying. There's a level of confidence that overexceeds the value of the information he is disseminating. If he were the lead engineer of a project, would he make bad decisions because of stuff like this? My guess is yes.
- tibbar 1y ago
  It's definitely an overly-broad generalization. But these mental models would still improve how many product engineers work with databases, at the expense of a very simple explanation. I think the interesting question is like, if I have X amount of time and mental bandwidth to learn about a technology, what's the most helpful lossy compression of concepts that fits?
  - sgarland 1y ago
    > I think the interesting question is like, if I have X amount of time and mental bandwidth to learn about a technology, what's the most helpful lossy compression of concepts that fits? You cannot possibly know if your condensed version is accurate or sufficient if you can't point to the author of it and definitively state that they knew the original material well enough to summarize it. I also continue to push back on the idea that [backend] devs shouldn't need to know SQL extremely well, and as an extension, their RDBMS vendor's implementation of the spec. You have to know your main programming language to get the job; why should the part of it that stores and retrieves all information for your application somehow be lesser? If you don't want to, then you don't get to write queries and design schemas, period. Access them via an API that's been designed by domain experts, otherwise you're putting everyone at risk.
    - tibbar 1y ago
      > You cannot possibly know if your condensed version is accurate or sufficient if you can't point to the author of it and definitively state that they knew the original material well enough to summarize it. I don't understand how this relates to the article. The author is not presenting himself as an expert on database indices, nor is the purpose of the article to educate people on database indices. If anything, he's illustrating techniques for dealing with technology when you're not an expert. > I also continue to push back on the idea that [backend] devs shouldn't need to know SQL extremely well The article isn't about any particular technology or type of software engineer, either -- this is just an example. We all have to use technologies that we're not world-class experts in, and part of being a professional engineer is learning ways to deal with that sad reality. -- Edit to add: I do feel a certain sympathy/resonance for your claim that people should be really competent at the tools/tech they're using, and it's strictly "better" for everyone. But we also live in a world where the complexity and depth of software stacks is increasing rapidly, and developers often have to prioritize breadth over depth. (And yes I have seen a lot of people shoot their foot off with poorly-informed use of databases :/)
      - sgarland 1y ago
sgarland 1y ago
MySQL and Postgres both support it; that's a huge percentage of what most devs are ever going to encounter. MSSQL and Oracle may do so as well, but I'm not familiar with those beyond some trivial usage. More to the point, this lack of knowledge will almost certainly drive people to over-index, which harms performance.

by tibbar 1y ago

rileymat2 1y ago

You have me curious which databases would not make this optimization, it seems like table stakes?

tibbar 1y ago
For example, many NoSQL databases are less sophisticated in query planning.

blindriver 1y ago

The point is that there's misinformation in the things that he's saying. There's a level of confidence that overexceeds the value of the information he is disseminating. If he were the lead engineer of a project, would he make bad decisions because of stuff like this? My guess is yes.

tibbar 1y ago
It's definitely an overly-broad generalization. But these mental models would still improve how many product engineers work with databases, at the expense of a very simple explanation. I think the interesting question is like, if I have X amount of time and mental bandwidth to learn about a technology, what's the most helpful lossy compression of concepts that fits?
- sgarland 1y ago
  > I think the interesting question is like, if I have X amount of time and mental bandwidth to learn about a technology, what's the most helpful lossy compression of concepts that fits? You cannot possibly know if your condensed version is accurate or sufficient if you can't point to the author of it and definitively state that they knew the original material well enough to summarize it. I also continue to push back on the idea that [backend] devs shouldn't need to know SQL extremely well, and as an extension, their RDBMS vendor's implementation of the spec. You have to know your main programming language to get the job; why should the part of it that stores and retrieves all information for your application somehow be lesser? If you don't want to, then you don't get to write queries and design schemas, period. Access them via an API that's been designed by domain experts, otherwise you're putting everyone at risk.
  - tibbar 1y ago
    > You cannot possibly know if your condensed version is accurate or sufficient if you can't point to the author of it and definitively state that they knew the original material well enough to summarize it. I don't understand how this relates to the article. The author is not presenting himself as an expert on database indices, nor is the purpose of the article to educate people on database indices. If anything, he's illustrating techniques for dealing with technology when you're not an expert. > I also continue to push back on the idea that [backend] devs shouldn't need to know SQL extremely well The article isn't about any particular technology or type of software engineer, either -- this is just an example. We all have to use technologies that we're not world-class experts in, and part of being a professional engineer is learning ways to deal with that sad reality. -- Edit to add: I do feel a certain sympathy/resonance for your claim that people should be really competent at the tools/tech they're using, and it's strictly "better" for everyone. But we also live in a world where the complexity and depth of software stacks is increasing rapidly, and developers often have to prioritize breadth over depth. (And yes I have seen a lot of people shoot their foot off with poorly-informed use of databases :/)
    - sgarland 1y ago

sgarland 1y ago

MySQL and Postgres both support it; that's a huge percentage of what most devs are ever going to encounter. MSSQL and Oracle may do so as well, but I'm not familiar with those beyond some trivial usage. More to the point, this lack of knowledge will almost certainly drive people to over-index, which harms performance.