Hi,
I have a MyISAM table I’d like to move to innodb (v 5.0.62).
Unfortunately this table has the following structure (this is from an old version of FUDforum):
CREATE TABLE forum_thread_view ( forum_id int(10) unsigned NOT NULL default ‘0’, page int(10) unsigned NOT NULL default ‘0’, thread_id int(10) unsigned NOT NULL default ‘0’, pos int(10) unsigned NOT NULL auto_increment, tmp int(10) unsigned default NULL, UNIQUE KEY forum_id (forum_id,page,pos), KEY forum_id_2 (forum_id,thread_id)) ENGINE=MyISAM
The idea is that pos is incremented on each insert of the same forum_id and page, as shown in the following query:
INSERT INTO forum_thread_view (thread_id,forum_id,page,tmp) SELECT forum_thread.id, forum_thread.forum_id, 4294967294, forum_thread.last_post_date AS sort_order_fldFROM forum_thread INNER JOIN forum_msg ON forum_thread.root_msg_id=forum_msg.id WHERE forum_id=$forum_id ORDER BY sort_order_fld DESC, forum_thread.last_post_id DESC
After that, page is computed from the auto_inc pos field.
This is impossible to have such an auto_inc compound key on an innodb table.
I failed to find an optimized way to do the same thing (except with a temporary table) with an innodb table (and without the auto_inc, then).
Can someone help me rewrite this table?
Many thanks,