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.
Comments
“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 #
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 #
REPLACE INTO someTable (field) VALUEs (value)
REPLACE someTable set field = value
by rick on May 12 2006, 07:52 #