Wordpress wp-options table is huge, even after optimisation. Only dropping table and importing fixes

Matt Doble

8/21/23, 5:52 PM

I run a number of Wordpress based sites. In each case, the wp_options table is growing massively within MySQL, hosted in Azure.

In one case, phpMyAdmin shows the table size as 3Gb. If I export as an SQL file, it is around 20Mb. If I drop and re-import the table, it shows as 20Mb and everything works as expected.

There is nothing huge in the original 3Gb table, the biggest rows are nowhere near enough to justify the space 'used'. Have used WP-Optimise, plus the table optimise function in phpMyAdmin, neither of which fix. Only dropping the table seems to sort.

Is there a better route to fix this than needing to export/import again, or better yet, does anyone have a solution?

0 + 0

mysql

wordpress

Score:0

Server

Rick James

8/21/23, 9:25 PM

OPTIMIZE TABLE does very little for performance.

Look in the table. You will probably find that one plugin is failing to clean up after itself. Beat on them !

if you need help is deciding what is in the table, do something like

SELECT * FROM wp_options ORDER BY RAND() LIMIT 20;

and show us the results.

After cleaning up the table, I recommend this plugin for further speeding it (and other) tables: https://wordpress.org/plugins/index-wp-mysql-for-speed/

0 + 0

Matt Doble

8/22/23, 10:06 AM

This my point though, I do this, and the data does not exists. There is not 3Gb of data there. Exporting the entire table only comes to 20Mb or so. Something is artificially inflating the table size.

Rick James

8/22/23, 8:27 PM

@MattDoble - Possibly some plugin added a zillion records, then deleted them. Depending on how phpadmin measures table size _and_ what your settings (eg, `innodb_file_per_table`) are, the 3G could be bogus.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Wordpress wp-options table is huge, even after optimisation. Only dropping table and importing fixes

TH: ตาราง wp-options ของ Wordpress มีขนาดใหญ่มาก แม้หลังจากการเพิ่มประสิทธิภาพแล้ว วางตารางและนำเข้าการแก้ไขเท่านั้น

RO: Tabelul Wordpress wp-options este uriaș, chiar și după optimizare. Doar eliminarea tabelului și importul de corecții

RU: Таблица WordPress wp-options огромна даже после оптимизации. Только удаление таблицы и импорт исправлений

VI: Bảng tùy chọn wp của Wordpress rất lớn, ngay cả sau khi tối ưu hóa. Chỉ thả bảng và nhập bản sửa lỗi

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.