At WP Engine, the easiest way to access and work with your database is through a tool called phpMyAdmin. phpMyAdmin makes it easy to securely view and edit data as well as run queries to update data in bulk. If you are looking to optimize your WordPress website, you will likely need to perform several actions in phpMyAdmin.
Accessing your database with phpMyAdmin
- Login to the User Portal
- Select the production environment name
- Click phpMyAdmin
- A new tab will open and you will see the phpMyAdmin interface load
- Click on the database name in the far left column
- wp_environmentname — The primary database for this environment
NOTE: The database called snapshot_environmentname refers to legacy staging.
Run a Query in phpMyAdmin
- Open phpMyAdmin
- Select the database you’d like to run a query on
- You must select a database from the left column first
- Select SQL
- Look for the database name again above the text field, confirm this is the correct database you intend to run a query on
- Write or paste your query in the text field
- Click GO
- Some queries will require a second confirmation in order to run
NOTE: If you are running UPDATE or INSERT queries, these will change or add data to your database. You should make a backup before doing this to be safe.
Custom Database Prefix
Queries must be run in SQL and will most likely reference specific table names. Tables will not always be named the same thing from site to site, however.
Database prefixes can be changed for security reasons and may have been done so by a previous host if you migrated the site. Changing your database prefix means all of your tables are titled something new and are not using default titles. A custom prefix can be any string of random characters.
If you are running a query you must ensure you are taking into account if a custom database prefix is being used in order to properly run a query.
The default WordPress database prefix is wp_ and default tables are titled like this:
In this example, there is a custom prefix of wp_zgs0q4pna9_ and the table names look like this:
In this example, there is a custom prefix of test_ and the table names look like this:
How to Find the Database Prefix
If you cannot clearly distinguish your table names and prefix just from looking at your tables, you will need extra steps to locate which prefix (and subsequently, which tables) you are active.
- Connect to your site’s filesystem using SFTP
- Download the file wp-config.php from the root directory
- Open this file and locate the line $table_prefix
- Your database prefix will be within the quotes
- In this example the prefix is wp_zgs0q4pna9_
NOTE: It is common but not required that your database prefix include a trailing underscore at the end, however it is not required.
How to Run SQL with a Custom Database Prefix
You’ve found a SQL query you want to run but you’ve discovered you have a custom database prefix so the command doesn’t work correctly. You’ll need to slightly modify the SQL query to work on your site.
Wherever you see wp_ in a SQL query, just replace it with your prefix. This may be more than one location if the query interacts with multiple tables.
For this example, we use the prefix:
Default query example:
SELECT SUM(LENGTH(option_value)) FROM wp_options WHERE autoload = 'yes';
This command targets the table wp_options but we need it to target the wp_zgs0q4pna9_options table. Replace wp_ with your custom prefix for the command appropriate for this site.
SELECT SUM(LENGTH(option_value)) FROM wp_zgs0q4pna9_options WHERE autoload = 'yes';
NOTE: We’ve used an example database prefix. Yours will be different and will need to be modified appropriately.