http://www.contentwithstyle.co.uk/feeds/rss/comments/104 Tue, 06 Jan 2009 07:48:21 +0000 Zend_Feed http://blogs.law.harvard.edu/tech/rss http://www.contentwithstyle.co.uk/content/quick-mysql-nice-to-know/#comment-641 http://www.contentwithstyle.co.uk/content/quick-mysql-nice-to-know/#comment-641
“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]]>
“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]]> Thu, 11 May 2006 11:19:42 +0000 http://www.contentwithstyle.co.uk/content/quick-mysql-nice-to-know/#comment-646 http://www.contentwithstyle.co.uk/content/quick-mysql-nice-to-know/#comment-646
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.]]>
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.]]> Thu, 11 May 2006 12:57:50 +0000 http://www.contentwithstyle.co.uk/content/quick-mysql-nice-to-know/#comment-651 http://www.contentwithstyle.co.uk/content/quick-mysql-nice-to-know/#comment-651
REPLACE INTO someTable (field) VALUEs (value)
REPLACE someTable set field = value]]>
REPLACE INTO someTable (field) VALUEs (value)
REPLACE someTable set field = value]]> Fri, 12 May 2006 06:52:27 +0000