automatic or manual optimization
we are humans more chances are there to foregetting, neglecting, overloading our mind other thoughts
I simple neglecting cause ms latency tend to loose million dollars ft amazon, google etc.
WordPress database optimization plugins
Wp rocket vs wp-optimize
wp optimize introduced page cache, minify css&js judt like other caching plugins.
why? maybe wp rocket introduced db optimization along with page caching.
Wp rocket database optimization settings
clean expired transcients
clean post auto revisions (you can limit post revisions in wp config php)
optimize tables
delete auto draft a
Empty trash comments
wp optimize has better feature viewing tables just like in phpmyadmin.
deleting tables that are left by uninstalled plugins.
both plugins have weekly automatic cleanup & optimize settings.
wordpress database optimization with phpmyadmin
some plugins like all in one wp security, redirection manager, etc
Leaves logs in database tables.
ex: ip login licked table has 10000 rows, but posts table has only 100 rows.
Why reducing database boosts performance
Reason: innodb buffer pool caching database in memory.
so we allocate less memory that means lower costs & higher performance.
object caching redis / memcached also requires memory.
Myisam locks entire table while a query performing new queries has to wait.
Innodb locks at row level ,
logging to database also cause iops issues tend lower performance.
why need phpmyadmin to empty the data of table.
deleting wordpress post meta with phpmyadmin
(don’t play with database)leave it if not necessary.
or take backup must otherwise you regret in case something goes wrong.
why post meta rows higher than posts.
yoast seo meta, review schema, job schema, author schema,
each plugin adds meta data about your posts.
this is the reason post meta has higher rows.
delete the post meta for unused plugins.
1st check the plugin.
SELECT * FROM wp_postmeta WHERE `meta_key` LIKE ‘%smpro%’
DELETE FROM `wp_postmeta` WHERE `meta_key` LIKE ‘%review%’;
or
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
SELECT * FROM your_prefix_postmeta pm LEFT JOIN your_prefix_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL; DELETE pm FROM wp_postmeta pm LEFT JOIN your_prefix__posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
or
SELECT * FROM your_prefixcommentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM your_prefix_comments ); DELETE FROM your_prefix_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM your_prefix_comments ); SELECT * FROM your_prefix_commentmeta WHERE meta_key LIKE ‘%akismet%’; DELETE FROM your_prefix_commentmeta WHERE meta_key LIKE ‘%akismet%’;
bulk delete wordpress comments pending & spam phpmyadmin
DELETE FROM wp_comments WHERE comment_approved = ‘spam’;
DELETE FROM wp_comments WHERE comment_approved = ‘pending’;
transients
are used to communicate, expired transcients not automatically deleted themselves, we need to delete them manually.
delete unused tables
Phpmyadmin select table.
and drop.
post revisions
define( ‘WP_POST_REVISIONS’, 3)
clean all post revisions after certain time.
it’s better install a db optimize plugin.
old databases gets worse without optimizing it.
-
Carries out automatic weekly (or otherwise) clean-ups
Expired Transients
Wp-optimize
optimizing through phpmyadmin
deleting tables of uninstalled plugins
wordpress only have 11 core tables.
Innodb table locks row while mousam locks table while mysql queries.
whole database has to cache inorder to read lower size database Higher speed.
optimize database tables
optim
delete spam comments
we need to define spam triggers , so comments go straight to spam folder.
DELETE FROM wp_comments WHERE comment_approved = ‘spam’;
If you are DBA or db admin you may run SQL commands in terminal
like optimize, repair, other tools & scripts helpful
optimizing queries etc.
read MySQL optimization & MySQL performance tuning
tools do better & error free cleaning rather manualy doing it.(higher chances forgetting)