mysql - On duplicate key ignore?

ID : 20397

viewed : 35

Tags : mysqlmysql

Top 5 Answer for mysql - On duplicate key ignore?

vote vote

97

Would suggest NOT using INSERT IGNORE as it ignores ALL errors (ie its a sloppy global ignore). Instead, since in your example tag is the unique key, use:

INSERT INTO table_tags (tag) VALUES ('tag_a'),('tab_b'),('tag_c') ON DUPLICATE KEY UPDATE tag=tag; 

on duplicate key produces:

Query OK, 0 rows affected (0.07 sec)

vote vote

88

Mysql has this handy UPDATE INTO command ;)

edit Looks like they renamed it to REPLACE

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

vote vote

70

Memcache is an excellent tool still and VERY reliable.

instead of looking at this issue from the perspective getting down the who is faster at the < 100 ms range, look at the performance per "class" of the software.

  • Does it use only local ram? -> fastest
  • Does it use remote ram? -> fast
  • Does it use ram plus hardddisk -> oh hurm.
  • Does it use only harddisk -> run!
vote vote

63

What memcached does that Redis doesn't do is least-recently-used eviction of values from the cache. With memcached, you can safely set as many values as you like, and when they overflow memory, the ones you haven't used recently will be deleted. With Redis, you can only approximate this, by setting a timeout on everything; when it needs to free up memory, it will look at three random keys and delete the one that's the closest to expiring.

That's the main difference, if you're just using it as a cache.

vote vote

59

You may also want to look at Membase.

http://www.northscale.com/products/membase_server.html

I have not used it, but it appears to be similar to Redis in that it is a memory-centric KV store with persistence. The major differences from what I can see are:

  • Redis has significantly more data manipulation capability (ordered sets, etc.)
  • Redis has a pending Redis Cluster project to add horizontal scalability
  • Redis has a single tier of data offload to disk (VM) based on a hybrid algorithm that considers both LRU and the size of the object.

  • Membase uses the memcached wire protocol - useful as an upgrade path for existing applications

  • Membase is set up to scale horizontally using a distributed hashtable approach
  • Membase can support multiple tiers of data offload using an LRU approach (very seldom used goes to disk, somewhat seldom stuff goes to SSD, frequent stuff stays in RAM)
  • Not sure about TTL capability in Membase.

The choice may depend on the degree to which your application can leverage the extra data manipulation functionality in Redis.

Top 3 video Explaining mysql - On duplicate key ignore?

Related QUESTION?