Changing BIGINT to INT

Question

We have a WP page running with a Buddypress network, which is highly dynamic. We are trying to identify every possible opportunity to reduce load and storage - and found that quite some tables use BIGINT values. E.g. the user table, and by doing so, all other relational tables which refer to the userid.

We will never ever have 4 billion (!!!!) users which should be the limit of a regular UNSIGNED INT. Therefore I raise the question, whether this could be an VERY easy way to reduce storage and maybe slightly decreasing load on the MySQL server by handling smaller pieces of information while doing queries.

I mean I understand BIGINT can make sense - e.g. for the postmeta and usermeta data, because every uploaded picture etc is treated as a post, and with it are coming a lot of metadata...

Is there any downside in changing all userid fields to INT? I mean, it's such a simple operation using PHPMyAdmin...

EDIT: Unsigned INT handles 4 billion, not 4m users (which I had initially written). So whoever thought to support WP with 4b users and thereby doubling space for all userid fields (in posts, usermeta etc), must have gone crazy :D

Answer 1

There should not be any downside. The user ID is cast to int in many places in the WP PHP files anyway.

But I don't think this is a real performance problem. If your queries are slow, it might be more useful to inspect each of them and look for opportunities to improve the queries or to add indexes. If your bottleneck is really IO on the file system of your DB host, consider finding a better DB server, because that will hit you in other cases too.

This issue has been raised on the WordPress Trac 14 years ago, and it was closed by Matt, the owner project leader …

in the hope that WP may someday be successful enough to need it

So if you ever get into the situation that your blog has every second person on the planet as a member, don't forget to send Matt a Thank You!

Answer 2

I'm late to this party, but anyway...

In the MariaDB / MySql world tables with half a megarow are considered "large". "Huge" tables are about 100 times larger, or more. Your table sizes are well within the capability of your tech stack.

By moving from BIGINT to INT, you save four bytes for each place an id value is stored. That will amount to a few tens of megabytes in your system. On a properly provisioned modern server like yours, you'll have a hard time measuring the performance or space improvement you'll get from this. And, cloud storage costs for 100 extra megabytes will cost something like US$0.005 per month (less than a dollar a decade).

Any savings you get from this BIGINT to INT change will be eaten up immediately if you don't do it right and have to do it again. Your time is valuable too.

WordPress gets extensive testing before each update. Why take on your own retesting burden when you don't have to? Changing id column definitions requires testing. And testing is an expensive pain in the xxx neck.

You might break some plugins or themes which assume BIGINT. Which plugins? Which themes? Who knows? You'll have to test each one carefully before using it.

Paradoxically, making database systems like MySql / WordPress perform better often happens by adding copies of your data, in the form of keys, not making the data marginally smaller. It's a well-worked-out space-time tradeoff.

I've created (and tested) a free plugin to create more efficient keys for WordPress. Here. https://wordpress.org/plugins/index-wp-mysql-for-speed/

WordPress does have trouble handling tens of thousands of users. The trouble comes from the way it figures out which users are administrators, editors, authors, and so forth. I have also created a free plugin to address that problem. https://wordpress.org/plugins/index-wp-users-for-speed/