Social Networks for the life of the world
Articles about the MySQL relational data management system.
People are using the Index WP MySQL For Speed WordPress plugin! That’s great. But it shoots into the dark: nobody can tell how much improvement they’ve seen except subjectively. That’s not good. It’s hard to improve something unless we can actually measure it. Two things can make a troublesome query. If it runs just once … Read more
Installing old versions of MySQL is a giant pain. An AskUbuntu contributor gave a workable procedure. Need to run MySQL 5.5? Download this VM. Running MySQL 5.7 is easier. Install Ubuntu 18.04, then do sudo apt install mysql-server-5.7 .
Rick James and I have cooked up a plugin, Index WP MySQL for Speed, to help optimize the way WordPress uses its MySQL database. It adds indexes to WordPress’s tables in MySQL to allow them to deliver data more efficiently without needing WordPress code changes. Users do not see any change in functionality, only in … Read more
In the last section, we populated our us_counties table and used it for some queries. Now it’s time to create some views of the data. With views we’ll be able to do more interesting things without retyping huge queries. We can use views as a foundation for more analysis. The first view we need is … Read more
Back in the Database Programming section of this series, we created the view named us_counties_new, showing the daily new case load. Recall that we first computed the seven-day running average of the total case loads in the us_counties_avg7 view, then used those numbers for the daily case loads. Having the daily case loads available lets … Read more
The Times’s data is here. https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-counties.csv If you’re using a browser, open the context menu (right-click) on that link and use the Save Link As … operation to put it into a file on your system. We’ll use it later to load the database. Use your database interface program to log in to your database … Read more
The New York Times newspaper publishes, every day, a file showing COVID-19 cases and fatalities for each county in the USA. They put their file on github here for anyone to use. So, let’s use it. Let’s download it and learn a few things about using SQL to explore data as we dig into it. … Read more
The mode of a bunch of numbers is the most frequent number in the bunch. A bunch of numbers can have more than one most-frequent number. Given a table tbl with columns category and value, this SQL returns the mode for each category. WITH freq AS ( SELECT COUNT(*) freq, value, category FROM tbl … Read more
It’s tempting, when in a hurry, to create a queue object in Javascript with an array. To enqueue something, push() it onto the array. To dequeue it, shift() it off. It works. But it’s slow, because shift() takes O(n) time where n is the length of the queue. Kate Morely cooked up an almost-as-simple but … Read more
Somebody asked whether it’s a good idea to encrypt database columns in a web application. My answer: “It probably won’t help much.” Why? Figure out your threat model Best practice: figure out your threat model before you spend time and money securing your system. If you build complex security measures without a clear idea of … Read more
