Multi DB Question About Affiliate DB Tables

Hello There

I would like to ask if there was not a better way of doing this :slight_smile:

I looked trough my DB Shards 4096 + 2 and in ever one i see a tables called:

affiliatedata
affiliaterecords
affiliatereferrers

I am not a DBA although i mess with the dbs to keep them clean.

SO recap there:
affiliatedata 4096 rows of wasted space in the db
affiliaterecords 4096 rows of wasted space in the db
affiliatereferrers 4096 rows of wasted space in the db

That is a total of 12288 rows of wasted space is it just me or is that a lot of stuff the poor MySql server have to trough just to make sure the user is a affiliate or not.

Now My suggestion is easy to say but super hard to do just have 3 tables in the Global DB and that is it, this way global gets hit and not the "blog" dbs.

Again just thoughts as i am not a DBA :slight_smile:

Thank you Kindly
Mark bloglines.co.za

  • Tom Eagles
    • Syntax Hero

    Hi @Mark de Scande

    Your idea would be a lot more efficient, but I am just wondering if this is because each blog is scattered across the tables and therefore the affiliate tables are set there because of this.

    I will pull @Barry in on this and see if there is an option on this, although i am not sure how they would tackle this and provide backwards compatability as it would involve quite a bit of data movement to accomplish.

    Cheers!

    Tom

  • Barry
    • DEV MAN’s Mascot

    Now My suggestion is easy to say but super hard to do just have 3 tables in the Global DB and that is it, this way global gets hit and not the "blog" dbs.

    If you change the config option on line 10 of affiliateincludes/includes/config.php or add the define to your wp-config.php file then it will use a set of global tables.

    It is, by default, set to allow each blog to have it's own affiliate system so has a set of tables for each blog.

  • Tom Eagles
    • Syntax Hero

    Hi There,

    Just checking in and i see that we are still waiting for a response from our dev @Barry , This can take longer than normal as our devs have many threads to anwer, but i am tagging him again in case this thread has somehow dropped off his feed.

    Cheers

    Tom

  • Barry
    • DEV MAN’s Mascot

    is this the line should it not be set to true ?

    You need to set it to 'yes' so it will be

    define('AFFILIATE_USE_BASE_PREFIX_IF_EXISTS', 'yes');

    Ps now what will be a super easy way to remove all the tables from all the DBs :slight_smile: ?

    The bad news is..... There is probably a way to do it with a mysql procedure (along the lines of http://www.sqlservercurry.com/2012/12/drop-all-tables-in-database-whose-name.html ), or via a php script that will cycle through and drop a table at a time. It will take a little bit of investigation / experimenting though.

  • aecnu
    • WP Unicorn

    Greetings Mark de Scande,

    We have not heard back from you as to the status of this issue.

    If you are still having an issue please let us know so that we may try to get you fixed up as soon as possible by choosing to check mark this ticket as unresolved below and posting any new errors or symptoms you are noticing.

    This action will also bring your ticket up front back in plain view again within the ticket system.

    Thank you for being a WPMU DEV Community Member!

    Cheers, Joe

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.