Announcement

Announcement Module
Collapse
No announcement yet.

innodb, auto-inc, unique composite indexes in counter tables

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

  • innodb, auto-inc, unique composite indexes in counter tables

    Well this question involves all the above. I'm still learning the proper ways of database design (I'm a web developer and not a DBA).

    I have some tables that will keep track of statistics in a counting fashion. I have yet to test these tables out against heavy traffic. I read as much as I could find on designing counter tables, but I wanted to get expert opinions on what issues I will encounter if these tables see heavy activity (inserts, then mostly updates). Or get suggestions on changes to table for higher concurrent inserts and updates.

    Here's an example of one of the tables:

    CREATE TABLE `stats_affiliates` (
    `as_id` int(11) unsigned NOT NULL auto_increment,
    `stats_date` date NOT NULL default '1999-01-01',
    `affiliates_id` int(11) unsigned NOT NULL,
    `ac_id` varchar(20) NOT NULL,
    `gifts_id` int(11) unsigned NOT NULL,
    `slot` tinyint(3) unsigned NOT NULL default '1',
    `referrals` int(11) unsigned NOT NULL default '0',
    `signups` int(11) unsigned NOT NULL default '0',
    `impressions` int(11) unsigned NOT NULL default '0',
    `conversions` int(11) unsigned NOT NULL default '0',
    `lottery_entries` int(11) unsigned NOT NULL default '0',
    `verified_leads` int(11) unsigned NOT NULL default '0',
    `payout` decimal(11,2) unsigned NOT NULL default '0.00',
    `revenue` decimal(11,2) unsigned NOT NULL default '0.00',
    PRIMARY KEY (`as_id`),
    UNIQUE KEY `uidx_date_affid_acid_offid_slot` (`stats_date`,`affiliates_id`,`gifts_id`,`ac_id`,` slot`),
    KEY `fk1_stats_affiliates` (`affiliates_id`),
    KEY `fk2_stats_affiliates` (`gifts_id`),
    CONSTRAINT `fk1_stats_affiliates` FOREIGN KEY (`affiliates_id`) REFERENCES `affiliates` (`affiliates_id`),
    CONSTRAINT `fk2_stats_affiliates` FOREIGN KEY (`gifts_id`) REFERENCES `gifts` (`gifts_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    In the above table, I have an auto-inc primary key and a composite unique key. The 'slot' field will have a random value between 1-10 but mostly will start off with always a value of 1 and will increase its range if I need more concurrent inserts. But in reality there will be more updates to rows than inserts after all the slots are used (from inserts) for the current date.

    The way I am inserting/updating rows into this table is shown by the following SQL statement (PHP vars in bold):

    INSERT INTO stats_affiliates
    (stats_date, affiliates_id, ac_id, gifts_id, slot, signups, payout)
    VALUES (CURRENT_DATE, {$this->_affiliateId}, {$ac_id}, {$this->_giftId}, FLOOR(1 + (RAND() * {$num_slots})), 1, {$signup_payout})
    ON DUPLICATE KEY UPDATE signups = signups + 1, payout = payout + {$signup_payout}


    My questions are:
    1. Will my unique composite key cause a problem before the table locking from auto-inc primary key?
    2. Should I get rid of the auto-inc primary key and promote my unique composite key to primary?

    The table was designed for quick selects and will be read from a backend application. And most likely from a slave DB. Oh, This database server will be also be acting as a master.

    Any helpful comments will be greatly appreciated.

    alex
Working...
X