Posts from 2018

  • Optimize MySQL/MariaDB Queries with STRAIGHT_JOIN

    Recently, I had to deal with an issue on DMOJ, where a page displaying 100 out of around a million elements took over 10 seconds to load. Naturally, I started investigating the issue.

    (Note: DMOJ uses MariaDB, but the same problem, as well as the eventual solution, should work the same on MySQL as well.)

    The first course of action, of course, was to see what the database was trying to do, by running an EXPLAIN query. For those of you who don’t know, if you have a query of the form SELECT x FROM y WHERE z, running EXPLAIN SELECT x FROM y WHERE z would show what the query is doing, without actually executing the query.

    A quick look at the EXPLAIN output showed that MariaDB first did a filter on a 2000 row table, and then joined in the table with a million elements. Then, the 100 wanted rows were filtered out. This query plan was quite horrifying.

    (Read more...)
  • Python 2 on Windows: Unicode Command Lines with subprocess

    On Windows, using Python 2’s subprocess module to launch a process with a unicode command line that is not strictly from the currently active ANSI code page (i.e. encoding mbcs) will be mangled. All characters that cannot be encoded by mbcs will, in fact, be replaced with ?.

    Obviously, this is can be resolved by switching to Python 3, but sometimes, converting to Python 3 is not yet an option. A terrifying prospect in 2018, but a problem nonetheless.

    I present the module uniprocess, which defines its custom version of Popen and friends to work around the problem. I hope it proves useful to you.

    (Read more...)
  • The fast way to install nginx.org debs on Debian

    I personally prefer the nginx.org packages for nginx over the ones that comes with Debian. They are usually newer and have a more sane amount of dependencies. I also prefer the conf.d system over the sites-available and sites-enabled system.

    The main challenge in installing these packages on Debian is the trouble you have to go through to get the PGP keys and sources.list set up. nginx.org does not present a good setup script. This has become a repetitive and annoying experience, so I present a series of commands to set it up quickly.

    For stable:

    curl https://nginx.org/keys/nginx_signing.key | sudo apt-key add -
    (codename="$(dpkg --status tzdata | grep Provides | cut -f2 -d'-')"; echo; for deb in deb deb-src; do echo $deb http://nginx.org/packages/debian/ "$codename" nginx; done) | sudo tee -a /etc/apt/sources.list
    sudo apt update && sudo apt install nginx
    

    For mainline:

    curl https://nginx.org/keys/nginx_signing.key | sudo apt-key add -
    (codename="$(dpkg --status tzdata | grep Provides | cut -f2 -d'-')"; echo; for deb in deb deb-src; do echo $deb http://nginx.org/packages/mainline/debian/ "$codename" nginx; done) | sudo tee -a /etc/apt/sources.list
    sudo apt update && sudo apt install nginx
    
    (Read more...)