Last time we created a simple, but not particularly useful, stored function. This time, we’ll create something a little more complicated and useful. (more…)

Previously, I gave a brief introduction into what stored routines actually are and gave some advantages of using them. Now, I’ll cover some of the basics for creating stored routines. (more…)

Stored routines are a relatively new addition to the MySQL server, and aren’t generally used by a lot of people. Although they can greatly simplify application code and ensure that all parts of a system behave in a consistent manner, creating and using stored routines can still be a daunting prospect to those who have never used them before. In this series of articles I will introduce stored routines, how to create (and use) them and the reasons for using them. (more…)

Binary logging is one of those options that is available to all, but in most cases is never turned on. Here is why I think it’s a good idea to turn it on. (more…)

Sometimes you may feel the need to re-order the fields in one of your tables - you shouldn’t really have to do this, but some people prefer a ‘neat’ schema. The most obvious way of doing this is to recreate the entire table, or drop the field and recreate it, but there is an easier way that doesn’t involve dropping fields or tables. (more…)

At some point you will encounter the error message “Too many connections” - given that the MySQL database server has three different connection limits, it’s important to know which limit you’ve hit, and what you can do about it. (more…)

The MySQL database server is used for both desktop and web-based applications, and so ships with default values that try to meet both of their needs. In some cases, what’s appropriate for one is not appropriate for another, and idle/sleeping connections is one of them. (more…)

Improving the performance of a system using the MySQL database server is not just about indexes and buffers - getting the most out of the system requires a wider view. We’ll start by looking at what needs to be considered when a system starts needing work to maintain or improve performance. (more…)

Without wishing to sound pedantic, they’re indexes build upon a prefix of the column’s data. (more…)

When deciding whether to create a new index, or to split a table, it’s useful to work out the cardinality of the data to see whether it’s worth doing. It’s possible to calculate the cardinality of the data in a column before creating an index. (more…)