Announcement

Announcement Module
Collapse
No announcement yet.

Compound primary auto_inc primary key rewriting for Innodb

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Compound primary auto_inc primary key rewriting for Innodb

    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,

  • #2
    Hi,

    Quote:

    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


    How did you create a MyISAM table with an auto_increment column, which is not the PrimaryKey?

    Quote:

    This is impossible to have such an auto_inc compound key on an innodb table.


    Should this be a working example of what you currently do?
    If i am not mistaken, this is impossible on MyISAM as well,..

    Comment

    Working...
    X