| I failed <100%. shrugs 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, #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. |
This is true for some situations. But understanding indexing and databases can save you from headaches that come from structural problems. Not every query performance issue can be solved by throwing more indexes on a table and using it.
It's the idea of prophylaxis. An ounce of prevention is worth a pound of cure.
And if you are using production as a testing environment and waiting for problems in production to tweak your queries, then you probably won't last long in software development.
Indexes are the basics. Everyone should have grasp of it at the basic level.