MySQL

The REPLACE [INTO] syntax allows us to INSERT a row into a table, except that if a UNIQUE KEY (including PRIMARY KEY) violation occurs, the old row is deleted prior to the new INSERT, hence no violation.

Sounds very attractive, and has a nice syntax as well: the same syntax as a normal INSERT INTO’s. It certainly has a nicer syntax then INSERT INTO … ON DUPLICATE KEY UPDATE, and it’s certainly shorter than using a SELECT to see if a row exists, then doing either INSERT or UPDATE.

via code.openark.org » Blog Archive » REPLACE INTO: think twice.

I've been asked to make some reports recently for some people in my company.

Almost all of them are quite heavy and hit the database pretty hard.

The thing is that these reports are usually generated once or twice a month, but they process a year's worth of data. Every time they are generated, they really upsets my poor production MySQL database.

I was thinking if there maybe could be a better way to just save the processed data in a table and update it every now and then.

via When pet projects bite back!: Reporting SpeedUp Trick.

Putting the MySQL information_schema to Use

The MySQL information schema database was introduced in the September issue of the newsletter. We’ve seen that the information schema allows us to access metadata in a much more flexible way than the MySQL specific SHOW commands. We owe this to the fact that the information schema allows us to query and format metadata using arbitrary SELECT statements. Let’s explore this feature, and try to put that to good use