Here’s a good introduction to composite primary keys by Jeff Smith. When you’re defining a database table that’s being used to relate two other tables (e.g., products_discounts to map discounts to particular products), you should always create it with a composite primary key (i.e., both the product_id and discount_id columns) rather than a single identity column […]
Mark Maunder benchmarked several performance tuning measures with a WordPress installation on a VPS server to show which are most effective. The biggest improvements were from installing PHP-APC, setting up Nginx to proxy concurrent connections for Apache, and using MySQL’s query cache.
Migrating multiple standalone installations of WordPress into Multisite can be relatively easy if you don’t need it to be perfect. The Codex has a basic guide and Stephanie Leary gave some more detailed tips at WordCamp Phoenix 2011. But I’ve been working on a project for a client who had half a dozen blogs with […]
The underscore is a special character in MySQL, and it can be used to match any character in comparison operations. If you’re not aware of this, it can throw you off when trying to search for it inside records.
You’d think I’d have this memorized by now, but I always mess up the syntax and have to spend a few minutes digging through man pages or wading through search results to find it.
tar -czf [output-file.tar.gz] [input-directory]/
mysqldump -u [username] -p [database name] > [filename]
If you want to repair tables in a MySQL database, the docs say that you can just run myisamchk –silent –force */*.MYI. That doesn’t work under all version of MySQL for Windows, though, so you may get an error saying, “Error: 22 when opening MyISAM-table ‘*.MYI’. ” If you do, you can run this command […]
If a hard drive fails while MySQL is trying to write to the disk, the database could get corrupted. If that happens then you might see the MySQL service crashing every time when the first request is made after it starts. The error log might display something like this: InnoDB: Database was not shut down […]
Ian Gilfillan’s article on using indexes in MySQL has a good explanation of how to use the EXPLAIN statement to analyze a query, and what to look out for. You can also check out Jesse Farmer’s optimization tips for a primer on more advanced techniques.
MySQL doesn’t provide a way to perform natural sorting on a string, so if you have numbers in your data you may get results like this Product 110 Product 120 Product 13 Product 140 There are a lot of hacks available, but all of the ones I’ve seen only work in specific circumstances. The only […]
Richard Lord wrote a good article explaining the various ways of handling dates and times with PHP and MySQL.