Social Networks for the life of the world
Articles about the MySQL relational data management system.
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 will 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
Suppose you’re working on a web application to support users in multiple time zones. How do you handle dates and times? If your users all live in one time zone, it’s easy. Simply store important date and time data in DATETIME or TIMESTAMP columns, and be done with it. But, what application has users in just one … Read more
MySQL’s FULLTEXT search feature works best when it’s used on a large corpus of text. That is, for best results you need many rows. When the text corpus is too small, full text searching often returns strange results: the indexing process tries to work out common words. If the amount of text being indexed is … Read more
