Using an effective_date column in MySQL

There are times when I blog about things just because it's a nice place to share knowledge about how to get things done that aren't always obvious or straightforward. This is one of those things.

I have a table of "stuff" that uses an "effective date" to determine when a particular entry should be available. In this case, I want the largest effective date that's also not greater than the current moment. Sure - I could use a start and end date, but that would be a waste because it implies I know when I will stop using returned rows.

Let's assume I am showing users time-sensitive information from the following table (please ignore the lack of additional indexing):

CREATE TABLE user_notification (
   id             MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
 , category_id    MEDIUMINT UNSIGNED NOT NULL
 , effective_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
 , is_active      TINYINT(1) NOT NULL DEFAULT 1
 , message        MEDIUMTEXT NOT NULL
);

Next, let's assume that I only want to display the most up-to-date messages that are active for a given set of categories. There is one option that works for me no matter how many categories I use. It requires me to select and join from my user_notification table twice. When combining this data with other parts of a select, it will wind up being two joins.

  SELECT NOW() INTO @compare_date;
  SELECT un.*
    FROM user_notification
      AS un

    LEFT
    JOIN user_notification
      AS un2
      ON un2.id = un.id
     AND un2.effective_date > un.effective_date

   WHERE un.effective_date <= @compare_date
     AND un2.effective_date IS NULL

This might look a little strange, but here's how it works. Notice that the left join simply re-joins the same table back against the select, but, it's attempting to find the largest possible effective_date. The select's where clause limits us down to rows that are smaller than the compare date (NOW()).

This is one of those queries that may not come easily at first, but once you've done it a few times, you'll be able to crank out similar queries with ease.

Giving credit where credit is due - Thanks to my co-worker Brian P. for turning me on to this particular method of solving a similar problem.

Review: Samsung Glyde phone

Rating: 2 (0=toss in trash, 10=give as nice gifts) in a DBA/IT operational environment.

This phone sucks. It has the worst user interface I've seen in any phone since I started using them more than ten years ago. What's wrong with this phone? Plenty.

The screen is heat sensitive (not touch sensitive) so accidentally allowing your face to touch the phone at one of the button points will activate the pad and likely send the person on the other end (and your ear, of course) a really annoying tone. Also, because it's heat sensitive, if you're into doing things outdoors when it's cold (I bike year-round), the phone may not be able to sense your keypresses in the event your fingers get cold enough. I suppose I could learn to live with these "features" if it weren't for my next major gripe.

Often, when I touch the pad, the phone thinks I'm pressing somewhere on the phone that's nowhere near where I'm pressing. For example, when in portrait mode, I unlock the pad (so I don't annoy the person on the other hand when the phone bumps my face), then want to click the end button. If I've typed any keys accidentially or intentionally, it isn't there, so I have to click on the "back" button. Assuming I can press that button (I often can't for the same reason that will become very apparent in a moment), often when the phone is cold, I press the end button on the pad and I can watch it display a depressed button two buttons to the left of the end button. When that happens, I am forced to either wait for the person on the other end to hang up, or power the phone off using a button on the side.

If you thought this phone had a standard USB-sized port on the side for charging, forget it. I thought I would be able to use this phone more like a PDA and transfer appointments to/from it, synchronize my settings with my laptop, and other handy similar things you can do with almost any PDA. I guess Samsung didn't think that was very important so (at least on my Verizon version), the only way I can sync things is through their web-based service and even that is only limited to contacts. :-(

The web browser sort-of supports Javascript, so using sites like Blogger.com are difficult at best (when you're turned off Javascript support in the phone). Of course, when you do that, you loose the ability to use the nice WYSIWYG editor we're so used to. Even if you turn off Javascript, you're limited to 2,048 keystrokes and you can't send the ENTER key like you can on a regular computer. How lame.

Signal with the Glyde on the Verizon network in and around Denver has been "okay" but definitely not great. My Qwest phone had fewer dropped calls and low signal issues than this phone. I am not sure if it's the phone, the network, or the locations I'm operating the phone from, but I'm not impressed at this point.

The Glyde also has some pretty tight restrictions on memory available. Just to copy a single CD-worth of 56Kbps .wmv files onto the phone, I had to add a MicroSD card to the phone. Want to listen to music privately? You'll need an adapter to bring your headphone size down if you're used to using the same headphones you use on your laptop or desktop computer.

The camera operation is "okay" as well, however, it often gets confused and enters recording mode when I just want a snapshot.

Having talked to a number of Verizon reps, I've learned that this phone is Verizons most problematic offering today.

Okay - having said all that "bad stuff" I think it's only fair to talk about some of the good (though difficult to find) things. I do like that it comes with both a wall-based charger and a USB cable to transfer music to the phone that can also act as a charger for the phone as well. I like the operation of the sliding mechanism and how it protects the keyboard. I also like that when viewing web pages, I can zoom in or out.

All in all, if I had known then (when I bought the phone) what I know now, I would never have even looked at the Glyde.

MySQL CMDBA Certification Tips

Taking (and passing, of course) both the MySQL 5 CMDBA (Certified MySQL Database Administrator) examinations are all that's required to become a MySQL 5 CMDBA.

What did I do to prepare to take the exams? It helped that I've been using MySQL for a number of years, however, that isn't a prerequisite. MySQL offers a certification class that does a fair job of preparing attendees for the exam. However, even my class instructor suggested attendees go back to the book to continue practicing the exercises given and reading materials from the book. The book he was referring to is the very same book you can get at your local book store - MySQL 5 Certification Study Guide.

Knowing what I know now, with my experience, I could probably have passed the exams without the class, though taking the class exposed me to information I wouldn't have easily learned otherwise. That additional knowledge helped me become a better CMDBA.

If I had it to do over again, I would likely have used this approach:

• Get the MySQL 5 Certification Study Guide
• Read each DBA chapter carefully
• Work through each of the exercises given in the study guide.
• Find someone who is already certified to help me verify my answers.
• Print a copy of the Q&A section on the included CD's
• Answer each question without looking at the included answers first.
• Study materials related to any questions I missed, then go back and take the sample questions again until I was able to pass easily.
• When a particular topic proved especially difficult, I would have made up my own multiple choice questions right from the details given in the study guide.

Remember that the exams are based on a specific verison of MySQL 5 that is documented in the study guide. Some of the capabilities of MySQL have changed since the book and corresponding exams were written. The answer the exam is looking for is based on expectations at that time, not based on current performance and capabilities.

Good news - when I took my MySQL 5 CMDBA examinations, I was allowed to review answers I had already given (or not given) before submitting the answers back through the system. If in doubt about a question, don't let it bog you down. Make a note on the question and come back to it with the whiteboard or paper the proctor will provide. That way, you can get as many questions answered as possible. If early questions take big chunks of time to answer, that can hurt you if you don't finish some of the questions later in the exam.

One other note, if you have difficulty with a particular question (i.e. it seems ambiguous), make sure you comment on the question explaining why you answered the way you did. If the question really is ambiguous, then when your exam results go for review, the reviewers will see your comments explaining your thinking. This can help improve the test.

Why did I take the exams? Besides gaining the certification, I wanted to improve my knowledge of MySQL 5 specifically. I also wanted something I could put on my resume that helped show that not only had I done work on MySQL 5 systems, but I became certified on them in the process. That has helped me with credibility in interviews.

Oh - one other thing I almost forgot - on the day of each exam - I actually chose to take off early from work, then spend an hour in the coffee shop near the exam site just to make sure I knew the answers to the questions I had previously missed. It proved helpful. I also took the night prior to the exams off just to make sure my mind could relax. I also made it easier on myself by scheduling my exams for the afternoon, and I stayed away from certain foods I knew would make me tired that day (like turkey sandwiches at lunch) and other foods that would make me nervous or jumpy (caffine drinks for example).

Good luck on your exams :-)

KB