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.

It seems there are two camps when it comes to SQL and how to do database optimizations - the “generic camp” and “the specialist camp”. While I don’t consider myself an extremist, I am absolutely in the specialist camp and this little post is an explanation of why.

SQL is a generic database langauge . There are a few different standards in use (the language has progressed over time), but the core of the SQL language is pretty much the standard in most databases. It’s probably also standard - in any database - that the SQL standard has been extended with database-specific extensions which provides optimizations, functions or other options not available in the SQL standard.

Through the mysql client:

update mysql.user set password=password('NEW_PASSWORD') where user='USERNAME' and host='HOSTNAME';
flush privileges;

Through the command line:

mysqladmin -u USERNAME -p CURRENT_PWD password NEW_PWD

Replace USERNAME, CURRENT_PWD and NEW_PWD with appropriate values.

Finding records that do not match between two tables.

          CREATE TABLE bookreport (
            b\_id int(11) NOT NULL auto\_increment,
            s\_id int(11) NOT NULL,
            report varchar(50),
            PRIMARY KEY  (b\_id)

          );

          CREATE TABLE student (
            s\_id int(11) NOT NULL auto\_increment,
            name varchar(15),
            PRIMARY KEY  (s\_id)
          );

          insert into student (name) values ('bob');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'A Death in the Family');

          insert into student (name) values ('sue');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'Go Tell It On the Mountain');

          insert into student (name) values ('doug');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'The Red Badge of Courage');

          insert into student (name) values ('tom');

 To find the sudents where are missing reports:

          select s.name from student s
            left outer join bookreport b on s.s_id = b.s_id
          where b.s_id is null;

              +------+
              | name |
              +------+
              | tom  |
              +------+
              1 row in set (0.00 sec)

 Ok, next suppose there is an orphan record in
 in bookreport. First delete a matching record
 in student:

       delete from student where s_id in (select max(s_id) from bookreport);

 Now, how to find which one is orphaned:

       select * from bookreport b left outer join
       student s on b.s_id=s.s_id where s.s_id is null;

     +------+------+--------------------------+------+------+
     | b_id | s_id | report                   | s_id | name |
     +------+------+--------------------------+------+------+
     |    4 |    4 | The Red Badge of Courage | NULL | NULL |
     +------+------+--------------------------+------+------+
     1 row in set (0.00 sec)

Assume you have the table “exams” in the database “test”.Then, the following will give you XML output if executed from the shell prompt with the “-X” option. For html output use the “-H” option.

mysql -X -e "select \* from exams" test

To dump data into a comma separated file use this:

  SELECT * INTO OUTFILE 'tablename.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY 'n'
  FROM tablename;

Replace tablename with the tablename of the table you which to dump to a file.