MySQL

Sometimes there’s a need to keep a simple log in a database. A common format could be a table with a layout like this:

log

• area (char)
• lognotice (char or text)
• logtime (timestamp when the event was logged).

Fetching

Fetching all log entries from a certain area is a simple matter of fetching by the area field, but when building a dashboard with the most recent entry from each area is slightly more complicated - the Query to fetch the data could typically look like this:

When playing the role of the DBA, it’s often useful to get a quick listing of how many rows each table in a database contains. The syntax for this is pretty simple in Mysql:

SELECT table_name, table_rows 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '***database name***';

Notice that when using innodb tables, it’s only a rough estimate.

Just a word of warning when using PHP and Mysql - if you’re trying to make efficient code and not utilizing all sort of frameworks and abstractions, you might be in for a small surprise in a default setting.

Usually is slightly lazy and often use the mysql_fetch_assoc function. It provides each row as an associative array, and is quite convenient to work with. Recently however while optimizing some code, I figured I’d switch to using mysql_fetch_array assuming it should be more efficient. The logic being that mapping hash keys to array values wouldn’t be needed and it should use less memory.

Mysql is a wonderful database, and while many use it, most people only scratch the surface of what the database can do. One of the practical functions available is the substring_index function, and an imaginary mailing list example is a nice way to show how to use it.

Let imagine we have a mailinglist in a table named “mailinglist” and it has a (char) column with the email addresses subscribed to the list. We now want to figure out how many users, that are subscribed from the various domains in the list.

If you’re a developer and use mysql, I’m sure you’re aware that it’s a database and it quite good at storing data, but one of the neat things about Mysql (and most other databases) is also their ability to provide meta-data on the contents of the database.

Most people know how to use the meta-data queries in the commandline, but if you want you can also use them in your (php/perl/some-other- ) language. Here is a quick guide to some of them.