Hmm, I got 5 out of 5 for the Postgres, but the questions seem pretty easy for anyone who has dealt with trying to create useful indexes in different databases and debugged slow running queries on an index.<p>`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 would guess that at least half of the programmers where I work would have failed. I wonder how competent developers are at other companies with their SQL databases?<p>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.<p>I just want to scream sometimes!!!
If you didn't get a 4 or 5 out of 5, you should go through this website, it's pretty awesome. It explains SQL performance really well, and provides equivalent examples for whatever database you use (including Oracle and DB2 in addition to MySQL and Postgres).
I got 4 out of 5 for mysql. Should have given it more thought :-)<p>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 ;-)
How to get 5/5: Visualize a B-Tree that indexes the stated columns in the stated orders. Is the query faster to answer by traversing that B-Tree (where faster means looking up fewer records, or not needing to look them up at all)? Is there a different B-Tree we could build that makes this even faster? If you have problems visualizing a b-tree: any somewhat balanced ordered binary tree has basically the same properties.<p>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.
I'm also getting 5 out of 5 for the Postgres, but I think these questions are helpful. I'm not a DBA, but just guess with my knowledge when I take the database implementation course.
I got 4/5 (background: C++/C# programmer who sometimes works with SQL queries). The one I got wrong was the first one; I thought the query optimizer would be smart enough to spot that the date function could be turned into a range query, but apparently not.<p>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.
I got 4/5 on PostgreSQL. I selected "Not enough information" on question 5, which is supposed to be the wrong answer. But I think it's the correct answer.<p>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.<p><a href="https://gist.github.com/felixge/af97c844cb1f24ac278e0357741c4ea9" rel="nofollow">https://gist.github.com/felixge/af97c844cb1f24ac278e0357741c...</a>
I was under the impression that unlike MySQL, Postgres can use two indecies at once when doing a query. So in MySQL you could do a query like `SELECT * FROM tbl WHERE a = 1 ORDER BY b` and you'd want an index on (a, b). But in Postgres you could have a separate index on a and b and you'd get the performance gain. In fact I thought in Postgres land you'd be better off creating them as separate indecies.<p>Is this info now outdated?
I failed <100%. <i>shrugs</i> The problem I have with tests like this is it uses syntax I've never used before and I'm not sure how it works. Also the real world involves testing out and seeing what works and what doesn't. For example,<p>#1
CREATE INDEX tbl_idx ON tbl (date_column)
SELECT COUNT(<i>)
FROM tbl
WHERE EXTRACT(YEAR FROM date_column) = 2017<p>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.<p>#2
CREATE INDEX tbl_idx ON tbl (a, date_column)<p>SELECT </i>
FROM tbl
WHERE a = 12
ORDER BY date_column DESC
FETCH FIRST 1 ROW ONLY<p>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.<p>#4
CREATE INDEX tbl_idx ON tbl (text varchar_pattern_ops)<p>SELECT *
FROM tbl
WHERE text LIKE 'TJ%'<p>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?<p>#5
CREATE INDEX tbl_idx ON tbl (a, date_column)<p>SELECT date_column, count(<i>)
FROM tbl
WHERE a = 38
GROUP BY date_column<p>Let's say this query returns at least a few rows.<p>To implement a new functional requirement, another condition (b = 1) is added to the where clause:<p>SELECT date_column, count(</i>)
FROM tbl
WHERE a = 38
AND b = 1
GROUP BY date_column<p>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.
5/5 for Postgres. I'd chalk that up less to being any kind of Postgres expert and more to having already read through most of that site (and having taken previous versions of the test).
2 out of 5 for Oracle. But I haven't touched SQL in years, so not surprising I don't remember much. I do have to say I would have gotten 2 more correct if I wasn't so impatient :)