Latest: buggy behaviour of parent:: in PHP 5.3.3

Content with Style

Web Technique

quick MySQL nice-to-know

by Matthias Willerich on May 11 2006, 07:41

While dealing with a project at work, I came across a couple of database issues that you all probably already know. A standard case of RTFM (by the way, nice reference lookup service here ). Nevertheless, as I never seemed to bother using them, I’m sure there’s some of you out there that’ll find this helpful. And, er, others will probably see this as an embarrassment of mine.

First: It’s proven: INNER JOINs are more effective than cartesian products . I never liked this way of abbrevating INNER JOINs, so I got suspicious and looked for a good reason not to do it. Although the explanation for it is very straight forward, I’m surprised that MySQL doesn’t optimize or translate this internally.

Then: I have a scenario where I’m working with primary and secondary keys, and I will more often than not insert 2 rows with the same ID and different secondary key(same content, 2 languages). In order to avoid checking for an item, then updating it, I first thought of using mysql_affected_rows() on an UPDATE. But this will return 0 if no changes were made, as well as if there was no row matching the filter.
Then I found INSERT…ON DUPLICATE KEY UPDATE but sadly I’m stuck with MySQL 4.0. The solution came around in the form of REPLACE .

Oh so simple. It’s an INSERT, and if the row is already there, it’s gonna be UPDATEd. As I already have the ID from a referencing table, I can merge INSERT and UPDATE for follow up items in other languages than the default one into one REPLACE statement.

Also: Why did I always have an ID in my joint tables? stupid. In most cases it will be enough to have both foreign keys as a combined primary key.

Is everyone using this? Is it working as I expect it to work? Am I gonna run into trouble with this further down the line? Time (and maybe one or the other comment) will tell.


  • I am not suprised that a specific join (inner, left or right) is somewhat faster than a cartesian product. As already stated in the link you give, the where clause is executed at the end as filter and not as first as restriction, with the use of join you will restrict your results first. What however is also remarkable of MySQL is this:

    “Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11.”

    So in versions before 4.0.11 you should use “INNER JOIN” and you were not able to use the “JOIN” for any natural joins.

    When using replace is can become rather ineffective if you have large tables (really large ;)) with indexes. While REPLACE INTO looks like it either performs an update or an insert it will executed in both cases as an insert. If the row exists it will first delete the row and after the delete it will insert the ‘new’ row. If you have a large table with rather large indexes both the insert as the delete wil have an impact on the rebuild of your index. Using a simple select to check if the row exists and if so perform an update is with large tables far more effectient and is worth benchmarking on small tables

    by rick on May 11 2006, 12:19 - #

  • I knew that REPLACE INTO would do that, but that’s fine in this case. It’s only executed when content is updated. And the tables won’t be that large, maybe a couple of thousand rows at the very very most.

    Also, with REPLACE I get to know the fact if a row was added or replaced, via mysql_affected_rows, which is subsequently either 1 or 2. Only that I have no use for it this time…

    Is there a difference between REPLACE and REPLACE INTO, it looks like it’s just a cosmetic thing?

    By the way, Rick, I had to slightly edit your comment, it somehow came out with mixed up lines.

    by Matthias on May 11 2006, 13:57 - #

  • There is no actual difference between REPLACE en REPLACE INTO it is more something the developer decides. You could do a replace into with the syntax of the insert statement or you could do replace with the syntax of the update statement.

    REPLACE INTO someTable (field) VALUEs (value)
    REPLACE someTable set field = value

    by rick on May 12 2006, 07:52 - #

Leave your comment

Comments are moderated.
Tags allowed: a, strong, em, code, ul, ol, li, q, blockquote, br, p