4 ms·

I've come to believe that designing systems like SQL to have queries that work transparently whether or not an index exists is wrong. There may be people that d

I've come to believe that designing systems like SQL to have queries that work transparently whether or not an index exists is wrong. There may be people that don't care how their query is actually executed, but I doubt it's the common case. So many times I've fought or watched other developers fight with getting a DBMS to use an index. Or been surprised to find out that production doesn't have that index but staging does. Or found out that someone added a lower() to a query that made the index stop working. Or found out that index turns out to be more expensive than scanning the table directly in a circumstance that is different between instances of the same software. Contrast that to an API like the Cassandra Thrift interface. You know if you're doing something expensive because the API only exposes the cheap things. It doesn't have an implicit sequential scan. You know you're doing one because you had to type out `while(next=...)` right in your code. Something as simple as SELECT mycolumn FROM mytable WHERE INDEXED b=5 vs SELECT mycolumn FROM mytable WHERE SCAN b=5 and refusing to run if the assertion isn't met would go a long way to making the API predictable

timtadh 11y ago
Most databases do have a way to force the index. See for example the MySQL way of doing it: https://dev.mysql.com/doc/refman/5.5/en/index-hints.html https://dev.mysql.com/doc/refman/5.5/en/index-hints.html . You probably already know this but I mention it in case someone else does not.
- kbenson 11y ago
  Does that actually fail on an index missing? The docs for FORCE INDEX say "a table scan is used only if there is no way to use one of the named indexes to find rows in the table", which to me implies it's more like FORCEFULLY SUGGEST.
  - meritt 11y ago
    If your force index names a non-existent index, you'll get an error.
rpedela 11y ago
> There may be people that don't care how their query is actually executed, but I doubt it's the common case. I bet it is pretty common. Personally, as long as the query is fast enough for my needs I don't care how it executes. I only start caring when performance sucks. I wouldn't be surprised if many other developers have a similar opinion.
- atomic77 11y ago
  Indeed, this is why the trend has been away from pure "nosql" solutions in the 2000s back to SQL-oriented systems. And not just in transactional systems but also batch processing, e.g. why would I write mapreduce code if I can just use a system like Hive that gives me a nice SQL-based interface to my data?
- ak4g 11y ago
  Confirming. Vast majority of SQL isn't written by developers or engineers to begin with, it's not even close. I administer a few DBs almost entirely queried by other departments, they'll ask for performance help (which, to date, has always consisted of eyeballing EXPLAIN output and adding an index) when something gets too slow. This happens maybe once every quarter.
  - protomyth 11y ago
    I think that depends. I was on a project where we absolutely as developers had to care about DB performance. We even wrote a tool for Sybase that monitored the query plans of long running queries. We discovered that the optimizer was really poor on Sybase 11.X and we needed to force indexes a lot. Once you learn that DB performance is not a gradual thing, you get into the grove of it. It goes from good to horrible once you hit that magic memory / table size. Truthfully, if your developers are the ones writing tools to figure out real-time performance, you probably want to start thinking about what's going on. (obviously this does not apply to the parent poster)
dragonwriter 11y ago
> There may be people that don't care how their query is actually executed, but I doubt it's the common case. I'm 100% sure its common. > Or been surprised to find out that production doesn't have that index but staging does. That's the same problem as having different application code on staging then what gets deployed to production; this isn't a problem with queries working independent of indexes its a problem of broken release processes, which requiring more ceremony to make queries work will not fix.
- krylon 11y ago
  > > There may be people that don't care how their query is actually executed, but I doubt it's the common case. > I'm 100% sure its common. The nice thing about the current state of affairs is that you can first build your queries / views until you get the results you need, then look what indices you can add to improve the performance of those queries. And if you get the results you need and find that performance is acceptable, you can just omit (or at least delay) the final step.

timtadh 11y ago

Most databases do have a way to force the index. See for example the MySQL way of doing it: https://dev.mysql.com/doc/refman/5.5/en/index-hints.html https://dev.mysql.com/doc/refman/5.5/en/index-hints.html . You probably already know this but I mention it in case someone else does not.

kbenson 11y ago
Does that actually fail on an index missing? The docs for FORCE INDEX say "a table scan is used only if there is no way to use one of the named indexes to find rows in the table", which to me implies it's more like FORCEFULLY SUGGEST.
- meritt 11y ago
  If your force index names a non-existent index, you'll get an error.

rpedela 11y ago

> There may be people that don't care how their query is actually executed, but I doubt it's the common case. I bet it is pretty common. Personally, as long as the query is fast enough for my needs I don't care how it executes. I only start caring when performance sucks. I wouldn't be surprised if many other developers have a similar opinion.

atomic77 11y ago
Indeed, this is why the trend has been away from pure "nosql" solutions in the 2000s back to SQL-oriented systems. And not just in transactional systems but also batch processing, e.g. why would I write mapreduce code if I can just use a system like Hive that gives me a nice SQL-based interface to my data?
ak4g 11y ago
Confirming. Vast majority of SQL isn't written by developers or engineers to begin with, it's not even close. I administer a few DBs almost entirely queried by other departments, they'll ask for performance help (which, to date, has always consisted of eyeballing EXPLAIN output and adding an index) when something gets too slow. This happens maybe once every quarter.
- protomyth 11y ago
  I think that depends. I was on a project where we absolutely as developers had to care about DB performance. We even wrote a tool for Sybase that monitored the query plans of long running queries. We discovered that the optimizer was really poor on Sybase 11.X and we needed to force indexes a lot. Once you learn that DB performance is not a gradual thing, you get into the grove of it. It goes from good to horrible once you hit that magic memory / table size. Truthfully, if your developers are the ones writing tools to figure out real-time performance, you probably want to start thinking about what's going on. (obviously this does not apply to the parent poster)

dragonwriter 11y ago

> There may be people that don't care how their query is actually executed, but I doubt it's the common case. I'm 100% sure its common. > Or been surprised to find out that production doesn't have that index but staging does. That's the same problem as having different application code on staging then what gets deployed to production; this isn't a problem with queries working independent of indexes its a problem of broken release processes, which requiring more ceremony to make queries work will not fix.

krylon 11y ago
> > There may be people that don't care how their query is actually executed, but I doubt it's the common case. > I'm 100% sure its common. The nice thing about the current state of affairs is that you can first build your queries / views until you get the results you need, then look what indices you can add to improve the performance of those queries. And if you get the results you need and find that performance is acceptable, you can just omit (or at least delay) the final step.