How To: Use MySQL Events

MySQL has a relatively new feature very similar to a crontab job (Unix/Linux) or task scheduler (Windows) for scheduling and executing tasks. Ever since the release of MySQL version 5.1.6 MySQL “Events” or “temporal triggers” have been included. So what are they? Well  you can now say: "I want the MySQL server to execute this SQL statement every day at 8:30am, until the end of the year" or anything similar that involves any number of SQL statements, and a schedule. How great is that.

Here's a simple example:

CREATE EVENT
e                               /* Event name */
ON SCHEDULE
EVERY 1 WEEK                    /* Interval */
DO
INSERT INTO t VALUES (0);       /* SQL statement */

The MySQL event scheduler is a thread that runs in the background looking for events to execute. It spends a lot of time sleeping — and won't do anything unless the new global variable "event_scheduler" is set to ON (1). So if you want to take advantage of this feature, do the following:

SET GLOBAL event_scheduler = 1;

To turn the feature off, do:

SET GLOBAL event_scheduler = 0;

I find MySQL Events extremely useful for creating and maintaining 'cache' tables with data integrity for very specific time intervals. This technique avoids having to query the large log tables to find data state at a specific time and date.

For instance, let's say you are administering a forum database and your client, the forum owner, has informed you that they want to begin monitoring a specific attribute of their forum and they have provided you with 3 monitoring time intervals; initialDate, midDate, finalDate. At these date and time intervals they want to know how many registered users last post date was on that day. Well, you could always query against the large 'post' table, however this forum is too large, and that query will bring the database to a crawl. So, a great option is to create a new table and use MySQL Events. You will need to create an 'event' with each date that will run the same query. Very easy.

Now, your client wants to further analyze their forum and has asked to be able to choose dates at their own will. Most solutions to the initial problem would entail your involvement each time your client wants to add a new date in this second problem. Well, by using Events and php, if you create an html form for your client to choose the group of dates and then utilize php and MySQL's integration, your form can create events on the fly for your client, satisfying their needs. Very easy again.
 

How To: Use MySQL triggers to log table changes

So you are looking for a simple way to log a history of changes to a table. There is a common practice for that, and it involves creating a logging function in your source code (maybe php) that will require you to connect to the database, and then write the values into the log table. However, I prefer using MySQL triggers for logging table changes for efficiency and simplicity.
 

CREATE TABLE data (
	  id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	  timestamp   TIMESTAMP,
	  data1 VARCHAR(255) NOT NULL,
	  data2  DECIMAL(5,2) NOT NULL
);

CREATE TABLE data_log (
	  action ENUM('insert','update','delete'),
	  id   INT UNSIGNED NOT NULL INDEX,
	  timestamp   TIMESTAMP,
	  data1 VARCHAR(255) NOT NULL,
	  data2  DECIMAL(5,2) NOT NULL
);

The data table contains all the information pertaining to the current data values. When an action occurs (such as an insert, update, or delete) to that table we want it logged to the log table, data_log.

You will notice 2 differences between the tables.

1. The id field has the nonunique index rather than being a primary key. This allows multiple rows per id value as primary keys are required to be unique values.
2. It contains an action field to indicate which type of MySQL statement was executed.

To ensure that changes to the data table are logged to the data_log table, we need to create a set of triggers. The triggers need to adhere to the following rules:

• For inserts, log an insert operation showing the values in the new row.
• For updates, log an update operation showing the new values in the updated row.
• For deletes, log a removal operation showing the values in the deleted row.

For this example we will specify AFTER triggers because we only want the log table to track successful changes to the data table (BEFORE triggers will activate even if row-change operation fails).
 

CREATE TRIGGER ai_data AFTER INSERT ON data
	FOR EACH ROW
	BEGIN
	  INSERT INTO data_log (action,id,timestamp,data1,data2)
	  VALUES('insert',NEW.id,NOW(),NEW.data1,NEW.data2);
	END;

	CREATE TRIGGER au_data AFTER INSERT ON data
	FOR EACH ROW
	BEGIN
	  INSERT INTO data_log (action,id,timestamp,data1,data2)
	  VALUES('update,NEW.id,NOW(),NEW.data1,NEW.data2);
	END;

	CREATE TRIGGER ad_data AFTER INSERT ON data
	FOR EACH ROW
	BEGIN
	  INSERT INTO data_log (action,id,timestamp,data1,data2)
	  VALUES('delete',OLD.id,NOW(),OLD.data1,OLD.data2);
	END;

NOTE: If you are performing this via phpMyAdmin the you will need to modify the above trigger creation code. The reason being that the semicolon causes an issue as it is usually defaulted to the Delimiter. Therefore, change 'END;' to 'END$$' for each trigger, and you will need to also change the Delimiter of phpMyAdmin to '$$'. The Delimiter field in phpMyAdmin will be located just below the SQL box in the browser.

A common question we hear about is, “Should we buy links?”

When SEO was in its infancy, on-page factors such as keyword density and meta tags were the end all and be all of ranking. However, once search engines overcame that hurdle they focused on incoming links, well at least Google did and everybody else soon followed. Now all it took was buying links from any website as the engines had not realized about weighting links yet. So with a fist full of money you could buy all the links required to place at the top.

But recently, buying links to obtain better search engine placement has been frowned upon and even forbidden. Why would seach engines frown upon purchased incoming links to a website you may ask? Well, it’s because purchased links unnaturally affect their organic listings. But are purchased links that bad? I tend to say they are not for everybody.

Google and other search engines have even taken preventive measures aginst purchased links. Rumour is they have been manually graphing paid link networks and devaluing link sellers by not allowing those links to pass Pagerank. However, when buying a link it’s always beneficial to examine the traffic that comes along with the link. It is almost always worthwhile to buy a link from a web page that is relevant to what your website is about.

On that note, one of the most immediate risks to buying links is your competitors. They are looking at what you are doing too and may report your purchased links to search engines. So my recommendation is to examine the link you are interested in, looking at value of site, reputation of site, ranking of site, domain tld and make sure to diversify your link portfolio. Paid links are huge business and it’s only going to grow. So don’t limit your link building efforts because paid links may be an important part of your niche.

Is Classic SEO dead?

I just read a great article detailing how classic SEO is dead. Personally I agree and disagree with the statement. Classic SEO, involving on-page factors, crawling, as well as link campaigns are still highly relevant in search rankings, however they are not the end all and be all anymore. Search engines have integrated image search, news search, video search and even local listings in the SERPs. A good example is Yahoo Glue (http://in.search.yahoo.com/). Try it out and see what the SERPs look like. Are the results more useful or relevant that a page of links? Maybe.

However classic SEO is never going to be dead. SEO tactics are used to rank a website at the top of the SERPS. When would a client not want that to happen?

Why do I agree with the statement that SEO is dead. First off, local listings don’t necessarily require crawling. These listings can and sometimes are taken directly from local directories, and videos from sites like Youtube rank without being crawled in the old sense of crawling. The new web and ranking systems have pushed much trust on to end users with tagging, ratings, and bookmarking.

So, I believe that SEO is still alive and kicking for assisting in ranking sites in SERPs, however, there are many means to rank sites/pages without using classic SEO tactics, especially in high traffic social websites. Now as SEOs we must provide our clients with new and innovative strategies to overcome the end user’s change in behaviour.

.ca Domain Names Will be Private

I have come across some interesting news concerning the registration of Canadiandomain names (.ca). The Canadian Internet Registration Authority (CIRA) will be implementing privacy changes to the dot-ca (.ca) WHOIS, effective June 10, 2008. Fot those who are not familiar with the WHOIS service, it’s a public tool that allows users to search information about a domain name (ie. registration name, address, phone number).

After numerous discussions and feedback from the public, CIRA adopted a new WHOIS policy that no longer displays this information, to provide greater privacy for the Registrants. As an individual Registrant, your private information will be protected by default – no action is required to receive this additional privacy feature.

Previously, (and still for .com, .net, … domain names) a register would need to pay a fee to protect their data by using services such as Domains By Proxy or Myprivateregistration and many others). I fully support the CIRA’s decision and hope one day that other domain tld’s decide to follow suit.

Learning Smiles – A Great Charity

Good Evening,

JIC Design is a proud believer in helping those in need from all parts of the world, and it all begins with great people working for great charities. We have had the honour to sponsor one of those wonderful charities, Learning Smiles, and were able to have a quick interview with founder Rob Gilmour.

What is Learning Smiles?

The goal of Learning Smiles is to purchase, collect and distribute learning supplies for children in developing countries.

How did it start?

After eating dinner with my family at Swiss Chalet one evening my children asked what they should do with the crayons they were given when we entered the restaurant. We have lots of crayons at home and really don’t need anymore. However, we told our kids to bring them home since we did not want to see them thrown-out and did not know what else to do. That is when the idea came to us that it would be great if there was some other option where we could donate these slightly used crayons to someone who needed them – plus the positive impact on the environment by keeping them out of the garbage. We made some calls and discovered that CIDA was involved in education projects in Peru and Cambodia and through connections any supplies we could collect would be distributed to schools that needed them – therefore the creation of Learning Smiles.

Has it been easy to start a charitable organization?

No, there is plenty of paperwork and the toughest part is the cost – about $1000 in fees to just register.

What projects have you started?

Our first project is a partnership with Swiss Chalet Restaurants Kingston, where we will collect the slightly used crayons provided to children at the restaurant. After their meal, customers will be invited to donate these crayons by dropping them into containers located at the entrance. Once these crayons are collected they will then be shipped to rural village schools in Peru and Cambodia. (Historically, Peru has had low levels of investment in primary education and one of the most ineffective and inefficient educational systems in Latin America. The current administration, however, is taking steps to correct this and we can help. Likewise, in Cambodia efforts are being made to provide education that helps children develop the skills they need to make positive changes in their lives, and finally break free from poverty.) We hope to collect 100 pounds of crayons by July.

Where would someone go to find out more about Learning Smiles or make a contribution?

We invite people to visit our website at www.learningsmiles.ca …….we are always glad to partner to raise more supplies for these schools.

Dashes Better Than Underscores For New Websites

At WordCamp 2007 Matt Cutts confirmed that dashes would be the best choice instead of no spaces or underscores. He mentined that Google was now looking into it. He recommends sites that already have underscores to leave them, as do I because that would be a 301 redirect nightmare, and for new sites to use dashes.