Hacker News Reader

Comment by oconnor663 | Hacker News Reader

I get that with some queries, you're not sure how the data is going to be retrieved, and letting the database figure all of it out for you is a good strategy. But with a lot of queries, particularly the ones that I'm doing in the milliseconds of a pageload, I want to be very sure that all my joins are hitting efficient indexes. I hate that someone can change my schema, and that can turn my efficient index lookup into a horrible scan, without breaking tests.

Folks who know more SQL than me: Is there a good way to say "I would rather this query fail than try to scan a table?"

View on HN

> I hate that someone can change my schema, and that can turn my efficient index lookup into a horrible scan, without breaking tests.

They can't, if you do perf testing as part of your pre-release testing, which you should do.

If you aren't doing perf testing, you are saying perf isn't an acceptance criteria, so why are you upset that breaking perf doesn't break tests?

Where you start to go wrong is where your talk starts to be of "YOUR" schema. The schema isn't yours, it's the company's. And guarding it is the DBA's job.

(I understand full well that that is a problem if the company has kicked out the DBA role and handed it over to the individual programmers, but perhaps that is precisely the problem.)

Fair, though maybe I could describe the same problem from the other end. If I'm a DBA and I want to change the way indexing is done, I might need to audit every query in my company to figure out which ones depend on the old index. One way or another, it seems like knowledge about a query's intent to use an index could've been captured explicitly, in a way that's automatically enforced in the future against any number of accidental breaks. But instead all that knowledge is implicit.

Integrating EXPLAINs in your build/deploy process should, in principle, make it possible for anyone to address any concern in such realms.

The DBA you were mentioning here, e.g., could first do a query in the EXPLAIN results, and he'll have his "company-wide queries audit" in a matter of seconds.

Reply on news.ycombinator.com