How to Optimize WordPress Database & Troubleshooting
List of Basic WordPress Tables
There are 11 tables found in brand new WordPress site-
(1) wp_posts, (2) wp_postmeta, (3) wp_options, (4) wp_users, (5) wp_usermeta, (6) wp_term_taxonomy, (7) wp_terms, (8) wp_term_relationships, (9) wp_links, (10) wp_comments, (11) wp_commentmeta
| Size |
+——————————————————–+——-+
| tble_actionscheduler_actions | 11 MB |
| tble_actionscheduler_claims | 0 MB |
| tble_actionscheduler_groups | 0 MB |
| tble_actionscheduler_logs | 8 MB |
| tble_bkash_transactions | 0 MB |
| tble_commentmeta | 0 MB |
| tble_comments | 3 MB |
| tble_gglcptch_allowlist | 0 MB |
| tble_links | 0 MB |
| tble_mailchimp_carts | 0 MB |
| tble_mailchimp_jobs | 0 MB |
| tble_op_user_templates | 0 MB |
| tble_options | 4 MB |
| tble_postmeta | 36 MB |
| tble_posts | 6 MB |
| tble_pprh_table | 0 MB |
| tble_revslider_css | 0 MB |
| tble_revslider_css_bkp | 0 MB |
| tble_revslider_layer_animations | 0 MB |
| tble_revslider_layer_animations_bkp | 0 MB |
| tble_revslider_navigations | 0 MB |
| tble_revslider_navigations_bkp | 0 MB |
| tble_revslider_sliders | 0 MB |
| tble_revslider_sliders_bkp | 0 MB |
| tble_revslider_slides | 4 MB |
| tble_revslider_slides_bkp | 0 MB |
| tble_revslider_static_slides | 0 MB |
| tble_revslider_static_slides_bkp | 0 MB |
| tble_smush_dir_images | 0 MB |
| tble_social_users | 0 MB |
| tble_sslcare_woo_alert | 3 MB |
| tble_term_relationships | 1 MB |
| tble_term_taxonomy | 0 MB |
| tble_termmeta | 0 MB |
| tble_terms | 0 MB |
| tble_tm_taskmeta | 0 MB |
| tble_tm_tasks | 0 MB |
| tble_usermeta | 6 MB |
| tble_users | 0 MB |
| tble_wc_admin_note_actions | 0 MB |
| tble_wc_admin_notes | 0 MB |
| tble_wc_category_lookup | 0 MB |
| tble_wc_customer_lookup | 0 MB |
| tble_wc_download_log | 0 MB |
| tble_wc_order_coupon_lookup | 0 MB |
| tble_wc_order_product_lookup | 1 MB |
| tble_wc_order_stats | 1 MB |
| tble_wc_order_tax_lookup | 0 MB |
| tble_wc_product_meta_lookup | 1 MB |
| tble_wc_reserved_stock | 0 MB |
| tble_wc_tax_rate_classes | 0 MB |
| tble_wc_webhooks | 0 MB |
| tble_wcpdf_invoice_number | 0 MB |
| tble_wdp_order_items | 0 MB |
| tble_wdp_orders | 0 MB |
| tble_wdp_rules | 0 MB |
| tble_wdr_order_discounts | 0 MB |
| tble_wdr_order_item_discounts | 0 MB |
| tble_wdr_rules | 0 MB |
| tble_wfblockediplog | 0 MB |
| tble_wfblocks7 | 0 MB |
| tble_wfconfig | 0 MB |
| tble_wfcrawlers | 0 MB |
| tble_wffilechanges | 0 MB |
| tble_wffilemods | 7 MB |
| tble_wfhits | 0 MB |
| tble_wfhoover | 0 MB |
| tble_wfissues | 0 MB |
| tble_wfknownfilelist | 3 MB |
| tble_wflivetraffichuman | 0 MB |
| tble_wflocs | 0 MB |
| tble_wflogins | 0 MB |
| tble_wfls_2fa_secrets | 0 MB |
| tble_wfls_settings | 0 MB |
| tble_wfnotifications | 0 MB |
| tble_wfpendingissues | 0 MB |
| tble_wfpklist_template_data | 0 MB |
| tble_wfreversecache | 0 MB |
| tble_wfsnipcache | 0 MB |
| tble_wfstatus | 0 MB |
| tble_wftrafficrates | 0 MB |
| tble_woo_wallet_transaction_meta | 0 MB |
| tble_woo_wallet_transactions | 0 MB |
| tble_woocommerce_api_keys | 0 MB |
| tble_woocommerce_attribute_taxonomies | 0 MB |
| tble_woocommerce_downloadable_product_permissions | 0 MB |
| tble_woocommerce_log | 0 MB |
| tble_woocommerce_order_itemmeta | 10 MB |
| tble_woocommerce_order_items | 1 MB |
| tble_woocommerce_payment_tokenmeta | 0 MB |
| tble_woocommerce_payment_tokens | 0 MB |
| tble_woocommerce_sessions | 1 MB |
| tble_woocommerce_shipping_zone_locations | 0 MB |
| tble_woocommerce_shipping_zone_methods | 0 MB |
| tble_woocommerce_shipping_zones | 0 MB |
| tble_woocommerce_tax_rate_locations | 0 MB |
| tble_woocommerce_tax_rates | 0 MB |
| tble_woodmart_wishlist_products | 0 MB |
| tble_woodmart_wishlists | 0 MB |
| tble_wpbkash | 0 MB |
| tble_wpbkash_refund | 0 MB |
| tble_wpfm_backup | 0 MB |
| tble_wpmm_subscribers | 0 MB |
| tble_ycd_subscribers | 0 MB |
| tble_yoast_indexable | 2 MB |
| tble_yoast_indexable_hierarchy | 0 MB |
| tble_yoast_migrations | 0 MB |
| tble_yoast_primary_term | 0 MB |
| tble_yoast_seo_links | 0 MB |
| tble_yoast_seo_meta | 0 MB |
Question: How to Optimize WordPress Database & Troubleshooting
Answer: (1) Your database is very bloated. The options table is at 25MB (I’d expect no more than 4MB) (2) The autoloaded dats is over 1MB (I’d expect about 600KB).
Question: How can I optimize a database of wordpress?
Answer: You can optimize under cPanel>>phpmyadmin>>select the database>>select all tables>>optmize.
2. Optimize WordPress Database with Plugins
Advanced Database Cleaner Plugin Features
Clean Database: WordPress database accumulates a lot of extra data such as spam comments, revisions, trashed comments, etc.
Optimizing database will reclaim unused space in tables, which will reduce storage space & improve efficiency when accessing tables. It does not run any code that break down site or delete posts, pages, comments, etc
Revision: WordPress record called “revision” of each saved draft or published item. It allows to see what changes were made in each post & page over time. It consumes a lot of space. SQP query to clean all revisions is: DELETE FROM posts WHERE post_type = ‘revision’
Auto Draft: WordPress saves your post/page when you are editing it before publish called an auto-draft. The sql query used by the plugin to clean all auto-drafts is: DELETE FROM posts WHERE post_status = ‘auto-draft’
Pending Comments: Pending comments are published by users & waiting for your. SQL to clean all pending comments is: DELETE FROM comments WHERE comment_approved = ‘0’
Spam Comments: It is a comment that marked as spam. SQL to clean all spam comments is:
DELETE FROM comments WHERE comment_approved = ‘spam’
Trash comment: A trash comment that you have deleted from your WordPress & have been moved to the trash. SQL to clean all trash comments is: DELETE FROM comments WHERE comment_approved = ‘trash’
Trackbacks: Trackbacks allows you to notify other websites have linked to their article on your website. These trackbacks is used to send huge amounts of spam. That is why they should be cleaned if you do not use them. The sql query used by the plugin to clean trackbacks is:
DELETE FROM comments WHERE comment_type = ‘trackback’
Pingbacks: Pingbacks allow you to notify other websites owners that you have linked to their article on your website. Trackback is basically the same things as a pingback. These pingbacks can be used to send huge amounts of spam. SQL to clean pingbacks is: DELETE FROM comments WHERE comment_type = ‘pingback’
Orphan post meta: The post meta data includes the author of the post, when it was posted. Sometimes post meta data information becomes orphan & does not belong to any post called “orphan postmeta” & should be cleaned since they are not useful. SQL to clean all orphan postmeta is: DELETE pm FROM postmeta pm LEFT JOIN posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL
Orphan comment meta: Orphan comment meta concern comments & not posts. SQL to clean all orphan comment meta is:
DELETE FROM commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM comments)
Orphan user meta: The user meta data is the information provide to viewers about each user. This information usually includes additional data that is not stored in the users table. Sometimes user meta data information becomes orphaned are called “orphaned usermeta” . SQL to clean all orphan comment meta is: DELETE FROM usermeta WHERE user_id NOT IN (SELECT ID FROM users)
Orphan user meta: It includes additional data that is not stored in the terms table and sometimes meta data information becomes orphaned. SQL to clean all orphan comment meta is:
DELETE FROM termmeta WHERE term_id NOT IN (SELECT term_id FROM terms)
Orphan Relationships: Sometimes the wp_term_relationships table becomes bloated with many orphaned relationships. This happens particularly often if you’re using your site not as a blog but as some other type of content site where posts are deleted periodically. Over time, you could get thousand of term relationships for posts that no longer exist which consumes a lot of database space. SQL to clean all orphan relationships is: DELETE FROM term_relationships WHERE term_taxonomy_id=1 & object_id NOT IN (SELECT id FROM posts)
Expired Transients: A way of storing cached data in DB temporarily by giving it a name. Expired transients are expired & still exist in the database.
Advanced Database Cleaner Plugin Benifits
- Remove old Revisions of posts & pages: WordPress stores a record called revision of each saved draft or published update. This system allows you to see what changes were made in each post and page over time
- Remove old Auto drafts : WordPress automatically saves your post / page while you are editing it. This is called WordPress auto draft. If you don’t hit the publish/update button, then the post/page will be saved as auto-draft and any modification to your post/page will not be visible in your public site. Over time, you could have multiple auto-drafts that you will never publish and hence you can clean them.
- Remove trash posts
- Remove pending comments
- Remove spam comments
- Remove trash comments
- Remove pingbacks
- Remove trackbacks
- Remove orphan post metadata
- Remove orphan comment metadata
- Remove orphan user metadata
- Remove orphan term metadata
- Remove orphan relationships
- Remove expired transients
- Display & view orphaned information before making a database clean-up so you can be sure about what you are going to clean-up
- Schedule database clean up to run automatically
- Display database tables information such as the number of rows, table size, etc.
- Optimize database tables
- Repair corrupted database tables or damaged ones
- Schedule database optimization &/or reparation to run automatically & specify what tables should be optimized &/or repaired
- Empty database tables rows
- Clean & Remove database tables
- Display options list
- Display options information such as option name, option value, option size, option autoload
- Clean & Remove options
- Set options autoload
- Clean & Remove scheduled tasks
Plugins Garbage Collector (Database Cleanup)
Some plugins create & use its own database tables. Those tables are left in your database after plugin deletion.
Database Management (Managed WordPress)
Question: What is the user name and password of phpmyadmin
Answer: You can get that information from the panel > WordPress > managed WordPress > manage > show credentials under “manage database”
Question: How to get login credential to upload files?
Answer: You can get that information from the panel > WordPress > managed WordPress > manage > show credentials under “manage database”
Question: My site is very slow and It is difficult to work so can u check?
Answer: Cache is not working currently, ttbx3_options | 26 MB | The optionts able earlier was at 26MB, which is very big. I cleaned it up so it is now 4MB. No, you would need to continually optimize / clean up. The possible plugins that may cache to go down -woodmart-core, woo-order-export-lite, wp-hide-security-enhancer, google-captcha. The possible plugins that may cache to go down.
Would it be okay to toggle these plugins for testing?
Database Management (Shared Hosting)
Question: Currently My Database size is large which is 800 Mb. As far I know my I am able to download upto 500 Mb data from server in my current hosting plan. Since it exceed upto 800 Mb, please help me to download it in File Manager. When download will complete I will download it from File Manager
Question: Today I have copy a database and folder of halal.hadisquran.com to make another sub domain named as boi.hadisquran.com.
At first I rename all url inside of autogar1_halaldp from halal.hadisquran.com to boi.hadisquran.com
After that I configured everything excluding htaccess file. Instead that My newly created subdomain boi.hadisquran.com is not working
Answer: Yes . Also, I’ll be very happy if I can help you with Safeguarding your account from Hackers, malwares etc. We actually have a service where our team of experts help customers all day safeguarding their accounts. The service is called Sitelock, where our specialists will recommend you the exact plan as per your requirements for the website security. So, Could you please let me know your Phone Number and Timezone so that I can ask our Experts to contact you?
Question: Who will be admin on Dedicated and VPS server?
Answer: The Dedicated server is self managed server, so once you purchase it you will be owner of it and you have all access to manage the server (b)
WordPress SQL
- Change Username: UPDATE wp_users SET user_login = ‘mashiur’ WHERE user_login = ‘admiin’;
| Subject | Link |
|---|---|
| SSH Command | help.dreamhost.com/hc/en-us/articles/4404267062164 |
| List of Basic Table | blogvault.net/wordpress-database-schema/ |
| Displays database name and size. | developer.wordpress.org/cli/commands/db/size/ |
| Clean Database | onlinemediamasters.com/clean-wordpress-database/#remove-trash |
| Clean Database Plugin | inmotionhosting.com/support/edu/wordpress/plugins/wordpress-advanced-database-cleaner/ |
| Clean Database Plugin (Dh) | wordpress.org/plugins/advanced-database-cleaner/ |
| Advance Database Cleaner | .youtube.com/watch?v=95kfb3bgRAI |
| Clean Database (Unused Table) | youtube.com/watch?v=2Xg1KOLQOpU |
| How to Open SQL Query in WP | youtube.com/watch?v=Yv0FDEfUsKw |
| WP SQL Commands | knownhost.com/wiki/resources/manage-wordpress-posts-using-sql |
| Database Optimization | sigmaplugin.com |
Trackbacks/Pingbacks