I read your other replies about the timestamp not working. I still
think adding the updated and created fields is a good idea in general,
to any table. I have some questions about the below since the
original suggestion would not work for you.
On Apr 2, 2009, at 12:35 AM, Andreas Pardeike wrote:
+---------+--------------+------+-----+-------------------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+---------+--------------+------+-----+-------------------
+----------------+
| id | int(11) | NO | PRI | NULL |
auto_increment |
| foo | varchar(255) | NO | | NULL
| |
| bar | varchar(255) | NO | | NULL
| |
| payload | longblob | YES | | NULL
| |
+---------+--------------+------+-----+-------------------
+----------------+
where 'payload' size usually is quite big!
Now I want to manipulate a number of rows in a non-trivial way and
at the
same time set 'id' for those rows to a new number in the sequence.
I.e.
UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo)
WHERE bar ...
My motivation is that this table has several consumers in other
applications
that keep track of the biggest 'id' they have seen between polls and
thus
can keep track of new and changed rows.
I am a little stumped on this, since id is auto_increment, do you
start to see gaps in your id's? This is not undesirable to you?
Right now, I solve this problem by copying the rows to a temporary
table,
then delete them and insert them from the temporary table but this
moves my
huge payload around which I really want to avoid.
How can I solve this in a better way?
How about changing the initial insert logic. The above table would
keep the id but be used as your key. Add a second id of payload_id.
Create a new table with id, payload_id, and payload. Store just the
payload in a separate table, connected with the id = payload_id. Now
you are never touching your payload data, which is too large.
Also, I have been in cases with batch uploads where performance is an
issue, and used the INSERT DELAYED features of mysql, where the the
insert is sent in one command, but the server will do it when there is
idle time to deal with it. You have to be a little careful to
anticipate a server crash or connection failure, but rollback can
solve that easily.
There does not seem to be an UPDATE DELAYED syntax, but I did just
learn of
The UPDATE statement supports the following modifiers:
• If you use the LOW_PRIORITY keyword, execution of the UPDATE is
delayed
until no other clients are reading from the table. This affects only
storage engines that use only table-level locking (MyISAM,
MEMORY, MERGE).
Maybe you can use the LOW_PRIORITY keyword in your update commands to
your advantage?
--
Scott * If you contact me off list replace talklists@ with scott@ *
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
