GamiPress makes use of some advanced database features to improve all it’s internal queries to make them the most faster as possible.

In this document, we take a look of the most important database features that GamiPress uses and everything you need to know and check to meet if your database is completely optimized.

Important: This document is for advanced users because requires knowledges about how to access to your database and run SQL queries. Always contact with your hosting provider before make any change.

GamiPress custom tables

GamiPress stores all it’s configuration and elements in the WordPress posts table, but for performance GamiPress stores its logs and user earnings in custom tables. The custom tables that GamiPress creates are:

• gamipress_logs
• gamipress_logs_meta
• gamipress_user_earnings
• gamipress_user_earnings_meta

For security, WordPress uses a prefix for all your site tables, by default this prefix is wp_ for that, if you check your database, you will see that all your tables has this prefix. For example, our gamipress_logs table should have been named as wp_gamipress_logs. Depending of your hosting, this prefix should be something completely random.

Database engine

Much hosting providers uses MyIsam as the default database engine. MyIsam is not a bad engine, but for complex queries InnoDB provides a better performance and speed.

GamiPress tries to create all its custom tables using the InnoDB engine, if you navigate to your database manager (like PhpMyAdmin) and you find that your GamiPress databases are using the MyIsam engine, you can change it running the following SQL queries:

• ALTER TABLE gamipress_logs ENGINE = InnoDB;
• ALTER TABLE gamipress_logs_meta ENGINE = InnoDB;
• ALTER TABLE gamipress_user_earnings ENGINE = InnoDB;
• ALTER TABLE gamipress_user_earnings_meta ENGINE = InnoDB;

In addition, we recommend to switch your WordPress tables to InnoDB too because this optimization change will be noticeable in all your site, for example, everytime a post, a list of posts or a user is queried. The most important ones to make the switch are the tables posts, postmeta, users and usermeta.

Note: If you are not sure about how to switch your tables to InnoDB, contact to your hosting provider and they will guide you with this.

Indexes

Indexes makes the database queries much faster, giving more importance to some table fields. For example, one of the most important fields of the gamipress_user_earnings is the user_id field which identifies to which user is the earning from.

Here are the SQL queries to add the correct indexes for each of the GamiPress database tables:

gamipress_logs

• ALTER TABLE gamipress_logs ADD INDEX type(type(191));
• ALTER TABLE gamipress_logs ADD INDEX trigger_type(trigger_type(191));
• ALTER TABLE gamipress_logs ADD INDEX user_id(user_id);

gamipress_logs_meta

• ALTER TABLE gamipress_logs_meta ADD INDEX log_id(log_id);

gamipress_user_earnings

• ALTER TABLE gamipress_user_earnings ADD INDEX user_id(user_id);
• ALTER TABLE gamipress_user_earnings ADD INDEX post_id(post_id);

gamipress_user_earnings_meta

• ALTER TABLE gamipress_user_earnings_meta ADD INDEX user_earning_id(user_earning_id);

Note: If you are not sure about how to run these SQL queries, contact to your hosting provider and they will guide you with this.