Real users slow applications down

Anybody who’s worked with databases knows this: as they grow they can slow down. It’s the curse of the customer base. Successful applications gather users and other transactional data. In this article I’m using MySQL database queries based on WordPress’s database design. Still, the ideas in this article apply to every application built on any … Read more

Capturing and examining queries

This entry is part 2 of 2 in the series WordPress MySQL performance monitor

It’s straightforward to get WordPress to capture the MySQL queries it uses to generate each page view. We can then analyze the captured queries. Capturing WordPress offers a way to capture a list of the database queries it uses in each page view. It’s simple: define the global SAVEQUERIES symbol. You can define it permanently … Read more

Analyzing WordPress database operations

This entry is part 1 of 2 in the series WordPress MySQL performance monitor

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

Legacy MySQL VMs

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 .

Speeding up WordPress database operations

The information in this post isn’t up to date. This was the original concept paper for the plugin. Please see here for the latest. 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 … Read more

Database programming

This entry is part 3 of 4 in the series Exploring data with SQL

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

Find peak cases

This entry is part 4 of 4 in the series Exploring data with SQL

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

Getting started with the data

This entry is part 2 of 4 in the series Exploring data with SQL

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

Computing the mode with SQL

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