Foreign keys vs partitioning

Question

Since foreign keys are not supported by partitioned mySQL databases for the moment, I would like to hear some pro's and con's for a read-heavy application that will handle around 1-400 000 rows per table. Unfortunately, I dont have enough experience yet in this area to make the conclusion by myself...

Thanks a lot!

References:

How to handle foreign key while partitioning

Partitioning mySQL tables that has foreign keys?

Answer 1

Well, partition is not a good solution for complicate data model. If you only have 2 to 3 tables depending on each other, you may be able to do it but it is not pretty. Each table must have an column that determine the partition. Then, each table must have a trigger to create the new table, set the foreign key and unique constraint.

For example, audittransaction<- auditentry

Each audittransactionhas 0 to n auditentry. table auditentry contains the foreign key of transaction. Both table have to have column creationDate since it is used for partition both tables.

------ create a trigger to insert audittransaction within the trigger

create or replace function audittransaction_insert_function() 
returns trigger as $$ 
DECLARE

    tablepartition varchar;
    tablename varchar;
    startbounds timestamp;
    endbounds timestamp;                


BEGIN
    tablepartition :=  to_char(date_trunc('month', NEW.whendone), 'YYYYMMDD');  
    tablename := 'audittransaction_' || tablepartition ;        

    if not exists(select * from information_schema.tables where table_name = tablename) then
        startbounds := date_trunc('month', NEW.whendone);
        endbounds := startbounds + cast('1 months' as interval);
        execute 'create table ' || tablename || ' ( CHECK (whendone >= ' || quote_literal(startbounds) || ' and whendone < ' || quote_literal(endbounds)|| ') ) inherits (audittransaction)';
        execute 'ALTER TABLE '||  tablename ||' ADD CONSTRAINT '||tablename||'_unique_id UNIQUE (id)';          
    end if;     
    execute 'insert into ' || tablename || ' (id, operationid, whendone, "comment", ticketid ,transactionid, userid )  values (' || quote_literal(NEW.id) || ',' || quote_literal(NEW.operationid) || ',' || quote_literal(NEW.whendone) || ')';                
    return null; 
END; $$ 
LANGUAGE plpgsql;

create trigger insert_audittrans

----- then, create a trigger for autientry

create or replace function auditentry_insert_function() 
returns trigger as $$ 
DECLARE
    tablepartition varchar;
    tablename varchar;
    startbounds timestamp;
    endbounds timestamp;                


BEGIN
    tablepartition :=  to_char(date_trunc('month', NEW.transactiontimestampgmt), 'YYYYMMDD');   
    tablename := 'auditentry_' || tablepartition ;

    if not exists(select * from information_schema.tables where table_name = tablename) then
        startbounds := date_trunc('month', NEW.transactiontimestampgmt);
        endbounds := startbounds + cast('1 months' as interval);
        execute 'create table ' || tablename || ' ( CHECK (transactiontimestampgmt >= ' || quote_literal(startbounds) || ' and transactiontimestampgmt < ' || quote_literal(endbounds)|| ') ) inherits (auditentry)';
        execute 'ALTER TABLE '||  tablename ||' ADD CONSTRAINT '||tablename||'_unique_id UNIQUE (id)';  
        execute 'ALTER TABLE ' || tablename ||' ADD CONSTRAINT auditentry FOREIGN KEY (audit_transaction_id) REFERENCES audittransaction_'||tablepartition ||'(id)';                
    end if;     
    execute 'insert into ' || tablename || ' (id, audit_transaction_id, eventid, transactiontimestampgmt,timestampgmt, acknowledged, resolved, acknowledgedbyusername, acknowledgeddate,  notificationlevel, resolvedbyusername, resolveddate, severity,  parentauditentry_id )  values (' || quote_literal(NEW.id) || ',' || quote_literal(NEW.audit_transaction_id) || ',' || quote_literal(NEW.eventid) || ','||quote_literal(NEW.transactiontimestampgmt)||')';             
    return null; 
END; $$ 
LANGUAGE plpgsql;

create trigger insert_auditentry before insert on auditentry for each row execute procedure auditentry_insert_function();

Answer 2

Well, if you need partitioning for a table as small as 400.000 rows get another database than MySQL. Seriously. By modern standards any table below 1.000.000 rows is normally neglegible in size (not even small), unless you also dont have any index etc. And modern standards are about 10 years old in this regard.