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 😀
2 Answers
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!
