4 ms·

The analysis of 28,000 results suggests that hardly anybody gets indexes right

46 points by mariuz 10y ago

gigatexal 10y ago
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.
falcolas 10y ago
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.
amelius 10y ago
> The analysis of 28.000 results suggests that hardly anybody gets indexes right Right, it is "indices", not "indexes" :)
- halfdan 10y ago
  Beg to differ: http://grammarist.com/usage/indexes-indices/ http://grammarist.com/usage/indexes-indices/ Both is acceptable.
  - ceautery 10y ago
    *are
    - halfdan 10y ago
      touché
guard-of-terra 10y ago
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.
lucb1e 10y ago
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.
leepowers 10y ago
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

46 points by mariuz 10y ago

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.

falcolas 10y ago

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.

amelius 10y ago

> The analysis of 28.000 results suggests that hardly anybody gets indexes right Right, it is "indices", not "indexes" :)

halfdan 10y ago
Beg to differ: http://grammarist.com/usage/indexes-indices/ http://grammarist.com/usage/indexes-indices/ Both is acceptable.
- ceautery 10y ago
  *are
  - halfdan 10y ago
    touché

guard-of-terra 10y ago

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.

lucb1e 10y ago

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.

leepowers 10y ago

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