One of the most critical tasks for a MySQL administrator is reliably backing up and restoring databases. This, of course, helps to prevent data loss in the event of a hardware failure, data corruption… or a little accident. Most modern hosting/server solutions have some kind of database backup solution available. However, many people prefer to…
MySQL can support large databases, but as the size of the data grows, the performance of your database can start to suffer. As a database administrator, there are a few things that you can do to improve the performance of your databases. The first place to start is with your hardware. As MySQL uses many…
One of the most valuable additions to MySQL 5 is the subquery, which lets you execute SQL like this: Unfortunately, MySQL 4 lacks this feature and is still prevalent in many web hosts. If you need to use a subquery, try the following workaround in PHP; Step by step: Get an array of user_ids from…
To get a random row from your table, you can use the following trick: This will return a single random row from myTable. Remember that you can also use a WHERE clause to narrow down the records before choosing a random one. This is a more efficient way of finding a random result than it…
If you want to reseed your primary key (i.e. change the value of the next primary key generated), you can do it like this: This is useful for starting a new site, and you don’t want to leave hints that you have just launched. For example, if you sign up a new user whose user_id…
Setting up indexes is essential to keeping your queries nice and quick. Adding an index is easy – if you’re using phpMyAdmin, just find your table, click “Create an Index” and choose your column or columns. Adding the right indexes is not easy and can be a bit hit-and-miss. This is where the handy EXPLAIN…
The PASSWORD() function in MySQL can generate a hash value for encrypted passwords, similar to md5() in PHP. It is generally not recommended to use this function in an application as it is not particularly secure – it is intended for internal use. If, however, your application uses the MySQL PASSWORD() function, you need to…
A common problem using the UNION command in MySQL is when you want to order data sets differently. If you do it incorrectly, you will see the error message: Incorrect usage of UNION and ORDER BY To solve the problem, wrap each SELECT statement in parenthesis as follows: Nice and simple; all you have to…
If you’ve ever had to transfer a large MySQL database, you know it can be a real pain. phpMyAdmin is the go-to for most web hosts, but when it comes to importing that massive SQL dump, it can be a big time-waster. Timeouts and errors are all too common when you’re trying to import data…
