Social Networks for the life of the world
Articles about the MySQL relational data management system.
Scalability Pro is a paid plugin for improving backend WordPress and WooCommerce performance. It does multiple things including query rewriting and memoization caching of some time-consuming database queries. One thing it does is add keys (indexes) to some WordPress tables. This article is a table-by-table analysis of some keys it adds, compared with the keys … Read more
Background WooCommerce offers a search box on the WooCommerce -> Orders page. It lets a shop manager search orders, current and historic, for the customer’s name, address, email, and other data. It also searches the names of products and shows orders containing those products. On large sites with many orders, the search is astoundingly slow. … Read more
Background When presenting so-called “archive” pages of content to users WordPress uses the notorious SQL_CALC_FOUND_ROWS MySQL extension to populate its display that looks something like this. This kind of user interface is known as “pagination” because it lets the user select pages of results. So, it’s really useful. The phrase “Showing 1-12 of 1348 results” … Read more
Index WP MySQL For Speed, the WordPress plugin, allows users to monitor their MariaDB or MySQL database traffic and upload the results of those monitors. It gathers information about the queries, including how long they take and how many there are. Analyzing the uploaded monitor data allows me to identify the most frequent queries and … Read more
During its occasional and automatic core version updates (for example from version 5.9.2 to 5.9.3) WordPress inspects its tables in its database server (MariaDB or MySQL) and tries to restore them to WordPress’s standard schema. Sometimes a site owner or plugin customizes those tables. For example my Index WP MySQL For Speed plugin reorganizes some … Read more
This is the data definition language used by the Index MySQL For Speed plugin to add and remove high-performance keys. This is the code used for recent versions of MariaDB and MySQL, the ones supporting the Barracuda version of the InnoDB storage engine. Notice that each table gets just one ALTER TABLE statement. This allows … Read more
This is the data definition language used by the Index MySQL For Speed plugin to add and remove high-performance keys. This is the code used for older versions of MariaDB and MySQL, the ones supporting the Antelope version of the InnoDB storage engine. Notice that each table gets just one ALTER TABLE statement. This allows … Read more
postgreSQL is, according to this article by Andy Pavlo, doing very well in design wins for new projects. Could WordPress adopt it? It would be hard to do. Even if the core could be adapted, the vast ecosystem of plugins and themes is very MariaDB / MySQL centric. Here are some musings from the core … Read more
Do you need to install an old version of MariaDB on Ubuntu? Maybe you need to do some testing. Here’s how. Visit https://mariadb.com/downloads/ and choose your package and OS. You’ll download a tar fie. Extract the tar file with tar xf mariadb*.tar. Change your working directory to the extracted directory. cd mariadb*debs Set up your … Read more
Sometimes, upon adding indexes to MariaDb or MySQL tables with columns with zero-default dates you’ll get an error message like this: This happens because your server’s default sql_mode includes the NO_ZERO_DATE value. It is prevalent when working on WordPress indexes using phpmyadmin or some other MySQL client program. To prevent it, change the sql_mode before … Read more