MySQL

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…

Once you’ve inserted data into your MySQL tables, you’re going to have to change it at one point. The type of query that you use to update data is called an UPDATE query. It is used to change the values in rows that already exist. The syntax for the query is as follows: {code type=sql}UPDATE…

Now that you’ve got your MySQL database set up, it’s time to start inserting data into the MySQL tables. The query that has to be run in order to insert data is called an INSERT query. The syntax is basically as follows: {code type=”sql”}INSERT INTO table_name (table_column, …) VALUES (value, …){/code} The first parameter is…

Before you get your code up and running, you have to do a few things. One of the things that you will have to do is select the MySQL database. The reason that you have to select the database is that the script has to know which database to use. Within the whole MySQL server,…

Before you get going with your next big project, you’re going to have to create a database for it to use. Here’s a quick guide on the few different ways that you can create a database. The first method that you can use is a helper script. A really popular one for MySQL is phpMyAdmin….

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…