REPLACE INTO will actually perform a delete and then an insert, while INSERT … ON DUPLCIATE KEY UPDATE will perform an update (as the name suggests). I would think the latter would be faster. I have not done any performance testing between the two, but it only seems logical the update would be faster than the delete/insert. Please correct me if I’m wrong.

Since the two statements both end up with the same result, I’m not sure yet what the benefit of REPLACE into it.

Yes, I realize both are MySQL additions, so please don’t bother telling me they’re not ANSI standard.

References:

• INSERT… ON DUPLICATE KEY UPDATE
• REPLACE INTO

Although I have not updated the website, documentation, or how to for mysqlsla, I am tossing mysqlsla v1.4 “out there” to view or wget at:

http://hackmysql.com/scripts/mysqlsla-1.4

I don’t like to release new versions without having everything else updated too, but for various reasons I’m making an exception this time.

mysqlsla v1.4 is much better than v1.3 and a lot has changed. So much has changed that the entire script was essentially re-written. Without an updated doc or how to, I suggest starting with the –help command line op, then contact me if you have specific questions or issues. I will, eventually, update the website, doc, how to, and officially package this new version.

With the start of the new year there are around 20 new job openings at MySQL.  There are worldwide positions available for web developers, software developers, support engineers, consultants, trainers, QA engineers and more.  Take a look at www.mysql.com/jobs for more information.

• MySQL: Jobs
• NewsForge: Finding an Open Source Programming Job

One of the new features in upcoming MySQL 5.1 Server will be the Event Scheduler. This feature was implemented by Andrey Hristov within the scope of a master's thesis for the University of Applied Sciences in Stuttgart. We incorporated his contribution into our code base - you can read more about this feature in this article from Peter Gulutzan. Andrey is now employed by MySQL AB as a Software Developer. Lenz Grimmer asked him if he would be interested in sharing his story and provide some more background about himself.

By Allison Randal

If you have the time to spare, it's definitely worth picking up a copy of Optaros' Open Source Catalogue 2007 (it's free). Most valuable is their perspective on current trends in the top-ranked projects: which are on the rise, steady, or on the decline. A few tasty tidbits:

The virtualization software XEN has positioned itself as a low cost and innovative alternative to commercial products such as VMWare. With every release stability and applicability are increasing.

And:

The databases MySQL and PostgreSQL (respectively EnterpriseDB as the professional derivate of it) have almost caught up with the benchmark of Oracle in terms of functionality and performance. In many situations they might even fit much better than the industry leader. …

What is often underestimated is impact of MySQL Performance by complex queries on large data sets(ie some large aggregate queries) and batch jobs. It is not rare to see queries which were taking milliseconds to stall for few seconds, especially in certain OS configurations, and on low profile servers (ie having only one disk drive) even if you just have one such query running concurrently.

Lets talk a bit how it is happening and how to prevent it.

Cache Wiping This is first reason for this to happen - query which crunches large amount of data set wipes data from your normal working set from OS cache. Operation Systems and MySQL Itself employs various strategies to attempt to minimize such effect but the truth is it still happens.

Disk Starvation As Cache efficiency drops more requests have to hit the disk, which may be 100% busy running your batch job query. This is especially bad when you …

I don't get it. I understand that Red Hat is a threat to operating systems companies everywhere, but I continue to find it highly ironic that its competitors proclaim cost savings for their customers...by shaving pennies from the least expensive part of the stack (the operating system).

Oracle did it with its "Unbreakable Linux." (Just picked up this shirt today, btw. Zack had it up on his blog and I thought it was too cool not to buy.) Oracle conveniently overlooked the fact that its database costs orders of magnitude more than the Linux the database runs on. If …

By Tim O'Reilly

Yesterday, I talked about the overall state of the computer book market. In this installment: category visualizations and trends showing which technologies are winning and which are losing in the book market. Here's a treemap view of the quarter on quarter differences between Q4 of 2006 and the same period last year:

As I've previously described in Book Sales as a Technology Trend Indicator, in a Treemap visualization, the size of a square indicates the relative size of the category, and its color indicates the rate of change. A category that is bright green is up significantly. One that is bright red is heading strongly in the …

I am happy to announce version 0.4 of mylvmbackup, a tool to perform consistent backups of a MySQL server's tables using Linux LVM snapshots.

For this release I'd like to especially thank Robin H. Johnson from the Gentoo project, who contributed another batch of useful changes and informed me that mylvmbackup is now in productive use to perform backups of the MySQL databases that power the project's Bugzilla bug tracking system. I am always glad to read about such use cases - how do you utilize mylvmbackup in your environment?

• The option handling has been improved. mylvmbackup now starts by using the builtin defaults, followed by the default configuration file (/etc/mylvmbackup.conf, followed by an alternative configuration file (specified via CLI …

At the MySQL Mini Conference in Sydney this week we discussed how to use EXPLAIN EXTENDED to view the rewrites undertaken by the MySQL optimizer.  IN particular, to see if MySQL performs a merge of the query into the view definition, or if it creates a temporary table.

It can be tricky to optimize queries using views, since it's often hard to know exactly how the query will be resovled - will MySQL push merge the text of the query and the view, or will it use a temporary table containing the views result set and then apply the query clauses to that?

In general, MySQL merges query text except when the view definition includes a GROUP BY or UNION.  But to be sure we can use EXPLAIN EXTENDED.  This also helps when we get confusing output in the EXPLAIN output.

For instance if we have a view definition like this:

CREATE VIEW user_table_v AS
     …