A couple years ago the company I worked for had had just launched a free consumer-facing application, and had been pushing it hard on social media. They were seeing hundreds to thousands of active users. It was painfully slow, and the culprit appeared to be the database. One of the main queries on the application was pushing the database server to its limits. Some users […]
Optimizing Away a LEFT JOIN in a Pinch Continue Reading »
A common problem I have worked on in my career has been to troubleshoot and improve slow, complex SQL queries. Usually these are part of codebases with few or no tests. If there are tests, they usually are testing some other part of the code and assume that the query is correct. It is rare to see tests that prove the columns being selected are
Quickly Testing A Refactored SQL Query for Equivalence Continue Reading »
From time to time, I have had to go back to a legacy database and for one reason or another add a non-integer surrogate key. Sometimes this has come up when working on an old API that exposes a primary key in the URL (GET /account/123). This generally can be bad for security, as a user could attempt to access other resources by incrementing the
Generating a Short Random ID in MySQL Continue Reading »
I’ve been doing a lot of tweaking of SQL queries as of late, trying to squeeze some performance out of a query by adding indexes, optimizing joins, and some other operations. To do a quick check to ensure that the queries return the same results, I’ve been hashing the results to see if I get an identical hash back. This solution was adapted from https://stackoverflow.com/a/13948327/206480.
Getting a quick hash of a query in PostgreSQL Continue Reading »
PostgreSQL has two functions that are not standard SQL, but are very helpful in certain situations : GREATEST and LEAST. In SQL MIN and MAX are aggregate functions that operate across all rows. But there are times where you want the smallest or largest value in a finite number of columns, a scalar min/max. That is where GREATEST and LEAST come in. Both allow for
PostgreSQL’s GREATEST (and LEAST) function Continue Reading »
