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:
- Will my unique composite key cause a problem before the table locking from auto-inc primary key?
- 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