
The analysis of 28,000 results suggests that hardly anybody gets indexes right - mariuz
http://use-the-index-luke.com/blog/2014-02/results-three-minutes-sql-performance-quiz
======
lucb1e
5/5, I am surprised and flattered. Probably more luck than anything else, I
expected to get 3 wrong, but I guess I was looking for the right thing.

Spoilers below.

Leaving some whitespace, since my eyes always glance further than I've
processed, like spoiler warnings.

The last question was between "does it do WHERE first or GROUP BY first?" In
real life I'd realize the problem and google the answer. I don't think it's
that bad if you got that answer wrong, so long as you realized that there's
something that could make a difference that you should google.

Kind of the same issue as with school: in real life you'd ask a coworker if
you don't know the answer on a test. In school you can't just look it up.
Silly system.

------
falcolas
I think that the problem is that "right" changes with use. Something which
works perfectly acceptable at 100,000 rows fails miserably at 100 million
rows. Write heavy tables will want different index use. Your DB engine will
also change what indexes you want to use.

DBAs may not be valuable for day-to-day work at most companies with the
prevalence of RDS and similar hosted DB offerings, but they are still
invaluable resources for periodic checkins.

Complete side note, but some of the defaults for MySQL RDS instances are
silly. They're inconsistent in what size of instance they target, and don't
follow best practices. Drives me batty.

------
leepowers
Just as an aside most DBRMS come with query profiling tools to help you
optimize and troubleshoot queries in relation to the schema. EXPLAIN in MySQL
has saved me many times, or at least enabled me to ask intelligent questions:
[http://dev.mysql.com/doc/refman/5.7/en/explain.html](http://dev.mysql.com/doc/refman/5.7/en/explain.html)

------
gigatexal
This site has been an invaluable resource for me as a DBA. Indexing isn't
intuitive at least for me but I've gotten better thanks to articles like this.

------
amelius
> The analysis of 28.000 results suggests that hardly anybody gets indexes
> right

Right, it is "indices", not "indexes" :)

~~~
halfdan
Beg to differ: [http://grammarist.com/usage/indexes-
indices/](http://grammarist.com/usage/indexes-indices/)

Both is acceptable.

~~~
ceautery
*are

~~~
halfdan
touché

------
guard-of-terra
SQL indexes are hard to get right and performance is often poor even when you
did.

On the other hand, for fulltext systems like Solr, indexes are trivial to set
up and performance is usually impressive, even if you never use any fulltext
features!

For many many uses, SQL is a poor fit, you're much better to stick to NoSQL or
full text searches.

Also: Everything you knew about database normalization is SLOW.

