large wp_options table in prod, but small in dev

i’m trying to solve the issue where the wp_options table is very large. my plan was to take a backup of the database, and restore it to my dev environment so i could test pruning it down. however, when i restore, i can’t get the same size results on disk in my dev environment that are in prod. i used the site health page in the tools section to see the overall size of the database, and then ran a database query to find out what were my largest tables.

i’ve tried exporting and restoring the database (using mysql workbench data export / restore) to my development environment so that i can test pruning the wp_options table, however, when i’ve restored an export of the database the wp_options table restores much smaller in dev than in prod- actually all of the tables restore much smaller. tables will have the same number of total rows, but the size is vastly different- like 4GB in prod to 10MB in dev.

i also tried restoring my dev environment via the all-in-one wp migration tool, but the result is the same. how do i get the same database size on disk across my environments? or how do i shrink the database size on disk in prod?

the query i’ve used for getting row size is below.

SELECT 
     table_schema as <code>Database</code>, 
     table_name AS <code>Table</code>, 
     round(((data_length + index_length) / 1024 / 1024), 2) <code>Size in MB</code> 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;