Web applications
Last updated:
How to flush MySQL query cache from time to time
Flush the MySQL or MariaDB query cache to defragment its free memory, manually on the MySQL shell or automatically through a Linux cronjob.
mysql
MySQL & MariaDB Database Administration & Optimization
Welcome to the database administration and performance tuning hub on saotn.org. In modern web architectures, the database is often the ultimate bottleneck. Ensuring high availability, rapid query execution, and bulletproof data integrity requires a deep understanding of server-side configuration, storage engines, and indexing strategies.
This section delivers practical, hands-on guides for managing MySQL and MariaDB databases in production environments. Whether you are optimizing low-level configuration files (my.cnf), automating routine server backups, upgrading database schemas, or troubleshooting slow-query bottlenecks within content management systems like WordPress, these articles provide verified, real-world blueprints.
Core Database Management Focus Areas
To help you maintain a fast and reliable database layer, our technical MySQL documentation is grouped by operational focus:
Performance Tuning & Caching: Eliminate database latency. Learn how to analyze slow query logs, tune InnoDB buffer pools, optimize memory allocation, and configure thread caches to maximize transaction throughput.
Backup, Recovery & Replication: Secure your enterprise data against corruption or data loss. Master command-line backup techniques, implement automated cron-job dump scripts, and orchestrate safe database restoration procedures.
Database Maintenance & Upgrades: Keep your database engine secure and compliant. Navigate version migrations safely, optimize database tables to reclaim storage space, and manage user privileges via secure access control baselines.
Application Layer Integration: Bridge the gap between your web server and data store. Discover best practices for configuring secure socket connections, tuning persistent database connections, and managing relational integrity for high-traffic web applications.
Browse the chronological archive below for deep-dives into SQL syntax execution, database engine conversions, and expert-level server-side troubleshooting.
WordPress
Last updated:
Wp_options table autoload micro-optimization
The autoload feature loads and caches all autoloaded options, if available, or all options. The default option is to autoload, and over time when the wp_options table grows, this degrades performance. Today I noticed a slow MySQL query coming from a WordPress database (WordPress wp_options table). This made me investigate and to optimize the WordPress wp_options table “autoload” feature.
Useful
Last updated:
Check, repair and optimize MySQL tables with mysqlcheck
MySQL's mysqlcheck is a command line table maintenance program: it checks, repairs, optimizes, or analyzes tables. Very handy for automated performance optimizations of MySQL databases and tables.
GNU/Linux
Last updated:
MySQL string comparison functions for MD5 and SHA1 hashes
MySQL string comparison functions for MD5 and SHA1 hashes; how to calculate MD5 and SHA1 hashes in MySQL and let MySQL do the calculations for you.
Web applications
Last updated:
Optimize all MySQL tables with PHP/MySQLi multi_query
PHP script to make your MySQL database smaller, leaner and faster by optimizing all tables with mysqli multi_query().
WordPress
Last updated:
Clean-up spam comments and meta data
Delete WordPress spam comments and meta data in your database, easily with phpMyAdmin or mysql cli. Keep your WordPress database lean & mean!
Codebase
Last updated:
Connect to multiple MySQL servers in a for and foreach loop
Check the connectivity of multiple MySQL servers, and connect to the servers in a loop. Here are two example code snippets
Codebase
Last updated:
Test MySQL database connectivity with MySql.Data.MySqlClient
MySql.Data.MySqlClient ASP.NET test script
Codebase
Last updated:
Optimize all MySQL tables with MySQLi multi_query
PHP MySQLi extension supports multiple queries which are concatenated by a semicolon. We can use this to optimize all MySQL tables in one single multi_query statement.
WordPress
Last updated:
Block WordPress comment spammers manually
The less spammers hit your WordPress blog, the better your blog performs, is one of my opinions. A second is, the less unnecessary plugins you use on your WordPress blog, the better. So, a little while ago I decided to remove plugins like Stop Spammer Registration Plugin and do its work myself.
WordPress
Last updated:
WordPress CMS admin password reset
Lost or forgotten your WordPress admin password? Reset it with a MySQL query, through functions.php over FTP, or with the lost password feature.
Web applications
Last updated:
Cache MySQL query results with PHP WinCache
Learn how to cache and store MySQL query results in your web server's RAM, utilizing the PHP WinCache user cache.
Web applications
Last updated:
Calculate MySQL database size with PHP (off the old shelf)
Use PHP, or a pure MySQL query on information_schema, to calculate the size of a MySQL database.
WordPress
Last updated:
WordPress HyperDB is an advanced database class that supports replication
HyperDB is an advanced database class for WordPress that supports replication and partitioning of data. It's a replacement for the standard wpdb class.
Web applications
Last updated:
MySQL query caching using PHP/Zend_Cache
By using the Cache.php class of the Zend Framework, you can relatively easily cache MySQL query results to disk for faster consecutive results.