4 ms·

I'm not a db expert, but this seems like something that should be solvable. It is very highly database specific. MySQL tends to be terrible. PostgreSQL tends

by btilly 6y ago

I'm not a db expert, but this seems like something that should be solvable. It is very highly database specific. MySQL tends to be terrible. PostgreSQL tends to be quite good. The reasons why it did not figure it out in particular cases tend to be complicated. Except for MySQL, in which case the problem is simple. There is a tradeoff between time spent optimizing a query and time spent running it. Other databases assume that you prepare a query and then use it many times so can spend time on optimizations. MySQL has an application base of people who prepare and use queries just once. Which means that they use simple heuristics and can't afford the overhead of a complex search for a better plan.

kristianp 6y ago
"MySQL has an application base of people who prepare and use queries just once". Is it really likely that mysql is unique in its usage patterns? Isn't it more likely that the devs have decided to keep things simple because that's the historical story for mysql. For example it originally didn't even support foreign key constraints if I recall correctly (that's a while ago now of course).
- dragonwriter 6y ago
  > "MySQL has an application base of people who prepare and use queries just once". Is it really likely that mysql is unique in its usage patterns? It's quite plausible that it is distinct in the usage patterns it has chosen to optimize for, and that that design choice has been instrumental in determining what use cases it became popular for and which use cases led people to migrate off of it for a different engine, yes. To the extent that is at play here it is somewhat of an oversimplification (even if the target audience was a factor in the sewing decision) to describe that as simple unidirectional causation from usage to design, and possibly more accurate (though still oversimplified) the other way around.
- btilly 6y ago
  Yeah, I'm pretty sure that it is unique to MySQL and SQLite. Back in the 90s and early 2000s, MySQL told people to just run queries. PHP encouraged the same. Every other database was competing on ability to run complex queries. And so it became common knowledge that applications written for MySQL didn't port well to, say, Oracle. Exactly because of this issue. Those applications and more like them are out there. And mostly expect to run on MySQL. So that use case has to be supported.
  - ComputerGuru 6y ago
    I agree on MySQL but hard disagree on SQLite because the API makes it rather obvious very quickly to any developer that there is a benefit to preparing beforehand.
bawolff 6y ago
I was under the impression that mysql wss smart enough to skip parts of the range that isn't needed. https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-skip-scan https://dev.mysql.com/doc/refman/8.0/en/range-optimization.h...
matwood 6y ago
> It is very highly database specific. I was about to comment the same thing. Every engine has its quirks, and the programmer learns them over time. I went from years on MSSQL to MySQL and it was a bit rough to be generous. But now I know many of the MySQL quirks and it's fine.

by btilly 6y ago

kristianp 6y ago

"MySQL has an application base of people who prepare and use queries just once". Is it really likely that mysql is unique in its usage patterns? Isn't it more likely that the devs have decided to keep things simple because that's the historical story for mysql. For example it originally didn't even support foreign key constraints if I recall correctly (that's a while ago now of course).

dragonwriter 6y ago
> "MySQL has an application base of people who prepare and use queries just once". Is it really likely that mysql is unique in its usage patterns? It's quite plausible that it is distinct in the usage patterns it has chosen to optimize for, and that that design choice has been instrumental in determining what use cases it became popular for and which use cases led people to migrate off of it for a different engine, yes. To the extent that is at play here it is somewhat of an oversimplification (even if the target audience was a factor in the sewing decision) to describe that as simple unidirectional causation from usage to design, and possibly more accurate (though still oversimplified) the other way around.
btilly 6y ago
Yeah, I'm pretty sure that it is unique to MySQL and SQLite. Back in the 90s and early 2000s, MySQL told people to just run queries. PHP encouraged the same. Every other database was competing on ability to run complex queries. And so it became common knowledge that applications written for MySQL didn't port well to, say, Oracle. Exactly because of this issue. Those applications and more like them are out there. And mostly expect to run on MySQL. So that use case has to be supported.
- ComputerGuru 6y ago
  I agree on MySQL but hard disagree on SQLite because the API makes it rather obvious very quickly to any developer that there is a benefit to preparing beforehand.

bawolff 6y ago

I was under the impression that mysql wss smart enough to skip parts of the range that isn't needed. https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-skip-scan https://dev.mysql.com/doc/refman/8.0/en/range-optimization.h...

matwood 6y ago

> It is very highly database specific. I was about to comment the same thing. Every engine has its quirks, and the programmer learns them over time. I went from years on MSSQL to MySQL and it was a bit rough to be generous. But now I know many of the MySQL quirks and it's fine.