mysql

we’re all familiar with the whole “thai food near me” thing. you type that phrase into your phone and it responds with a list of thai restaurants that are, well, near you. and we have a kind-of understanding of how that works under the hood: google or whoever has a database of thai restaurants with their latitudes and longitudes and knows our location from our phone and then does ‘some process’ to figure out which thai places are nearby.

in this post,we’ll be going over that ‘some process’ part, looking at how to use mysql to do some standard location stuff. we’ll cover mysql’s POINT and POLYGON types, finding the distance between two points on a sphere (which the earth, contrary to what you may have read on the internet, is), determining if a point is inside of a polygon defined by points, and look at things like ‘spatial reference systems’ which define how coordinates are plotted on the surface of the earth.

it’s a universal truth that one of the primary jobs of backend developers is drinking from the firehose of json the frontends are constantly spraying at us. normally, we pick apart all those key/value pairs and slot them into the neatly-arranged columns of our db, keeping our database structure nice and normal. but sometimes there are good reasons to just store a whole json object or array as an undifferentiated blob.

in the dark, old days, this json would go in a text column, which was fine if all we needed to do was store it and return it. but if we needed to do something more complex like, extract certain values from that json or, god forbid, use one in a WHERE clause, things could get out of hand pretty quickly.

fortunately, modern mysql supports JSON as a native data type and offers a whole host of useful json functions, allowing us to work with json data in a way that’s almost pleasurable. in this post we’re going to go over extracting values from json and using json data in WHERE clauses.

the cold, hard truth is that the reason your web app or api is running slow is because of your database calls. you can migrate everything to frakenphp or put compression in your http server if you want, i’m not going to stop you, but if you want to shave whole seconds off your response times, you should take a hard look at all that janky sql and yolo orm queries you wrote.

the good news is that mysql has a tool to find and log your slow queries. it’s called the ‘slow query log’, basically the perfect name, and it comes pre-installed. let’s get it turned on, set up, and look at the output.