The 3-minute SQL indexing quiz: Can you spot the five most common mistakes?

`EXPLAIN` is probably the most useful word in all of SQL. I didn't know how to read `EXPLAIN` results for the first few months of first starting to work with raw SQL for the first time. It made life so much easier when I could just take a query, throw an `EXPLAIN` in front of it, and have an answer as to what was being used to fetch my data.

I can tell you that as a production DBA when I'm brought in regarding a performance issue, the stuff that I see makes me wonder how some people remain employed as programmers. Full table scans on 50M row table, no problem. Query the database 10,000 times because you don't want to run one query and parse the results, no problem.

I just want to scream sometimes!!!

A suggestion: also mention that the correct results with an explanation are shown at the end. It was not clear to me and therefore I decided to answer just quickly. But maybe that is intentional ;-)

Indexes are not complicated (at this level of detail). Maybe the bad results (only 40% got >50% right) are mostly a testament to the state of computer science education around the world.

It's interesting to compare this with the 4th question, which more or less does exactly the same thing only with text instead of dates. They both have a WHERE clause that superficially tests the result of a function call but can be easily transformed into a simple range check on an indexed column. I don't know why the optimizer seems to be able to handle one case but not the other.

Depending on the distribution of the data in the table (e.g. all rows have a = 38, but no rows have b = 1), PostgreSQL will chose a Sequential-Scan instead of an Index-Only scan for both queries. This of course leads to the 2nd query being faster due to not having to aggregate anything.

https://gist.github.com/felixge/af97c844cb1f24ac278e0357741c...

Is this info now outdated?

#1 CREATE INDEX tbl_idx ON tbl (date_column) SELECT COUNT() FROM tbl WHERE EXTRACT(YEAR FROM date_column) = 2017

I've never used EXTRACT() in my life, so I don't know if it's index aware. I do know in real life I would write "WHERE date_column >= '2017-1-1' AND date_column <= '2017-12-31' or if I were querying a school_year or something that spanned between two years I would add another column and probably an index on that column and query by that not by the date_column.

#2 CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT FROM tbl WHERE a = 12 ORDER BY date_column DESC FETCH FIRST 1 ROW ONLY

Who uses FETCH FIRST 1 ROW ONLY instead of LIMIT 1? Also indexes can be ordered by ASC or DESC so there's a possibility of a small optimization there.

#4 CREATE INDEX tbl_idx ON tbl (text varchar_pattern_ops)

SELECT * FROM tbl WHERE text LIKE 'TJ%'

I thought the general philosophy regarding indexes was create the ones you know you need like on foreign keys and very common ones like last_name, SSN etc and then if you notice queries running slow add more and test. This seems like one of those examples. Do you really need an index here? How often are you making this query and what's the speedup gained if you add an index?

#5 CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT date_column, count() FROM tbl WHERE a = 38 GROUP BY date_column

Let's say this query returns at least a few rows.

To implement a new functional requirement, another condition (b = 1) is added to the where clause:

SELECT date_column, count() FROM tbl WHERE a = 38 AND b = 1 GROUP BY date_column

This seems like another case in the real world where you might test what the slowdown is, and how often you're running this query. If needed you can add another index or run a subquery first, but the answer to this question can be found out in < 10seconds in the real world more quickly by testing it than even spending the time thinking about it.

edit: MySQL