Reducing Database Load using Redis and Batched Insert/Update SQL Queries

I’ve been running my “T-Comm” Bus Locator website for over seven years now. As a quick recap, on the backend, it’s a procedural PHP/MySQL website, kind of a typical thing one would see of PHP development circa 2000s. The high level overview is that every minute or two, the system polls TransLink’s real time information APIs for the locations of the all the buses in the system. The system then calculates some information based on TransLink’s GTFS data, which has the schedule information for the entire system. Then it saves the data into a MySQL database, and some other outputs Continue Reading

Checking for a Null value in SQL

Yesterday I fell into a very simple but dangerous SQL trap.  Can you identify it? Found it?  It’s the comparison to null.  My intention was to get rows where another_column was null, however the query did not do so.  It didn’t give me any results where another_column was null, but didn’t give me any error either.  Why? Null in SQL is not a real value.  Null means “lack of a value” or “unknown”. Anything compared to null leads to an “unknown” result. Therefore, in SQL there are actually three values conditions can evaluate to: true, false, and unknown/null. This is called Continue Reading

Database query optimization is a useful skill

Recently I read an article titled “Quick tip: Improve developer habits by showing time cost of DB queries” by Dan Birken.  He claims that one common reason why websites load slowly are because of unoptimized database queries: Websites can be slow for many reasons, but an overwhelmingly common one is time spent performing database queries. Now database queries are likely a very important part of rendering the webpage – the information to populate a page has to come from somewhere. But sometimes rendering a page is slowed down by either unnecessary queries or improperly optimized queries. I completely agree with his view.  For most Continue Reading

Exit mobile version