2

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

2 Answers 2

2

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!

5
  • Thanks for your ideas - the thing is: We have huge tables with 500k rows, where the user id is referenced and also set as BIGINT. X tables, each 100k-500k*4bytes is something - especially when you consider that also the indices grow in size, and yes, unfortunately we cant fine-tune several mysql parameters, e.g. key_buffer_size. We have a lot of RAM and a good SSD, but phpmyadmin suggests that Mysql needs to read a lot from the disk because those thresholds are too low - that's why my idea was to save memory here.
    – tim
    Jan 4 at 8:26
  • BTW, interesting discussion going on here, just found it -> stackoverflow.com/a/9377107/701049
    – tim
    Jan 4 at 8:38
  • Yet another interesting part ronaldbradford.com/blog/…
    – tim
    Jan 4 at 8:40
  • 1
    WordPress.com has more users than the pagination query can handle yet it does not run into issues with INT vs BIGINT, this should not even be on your radar. It's a micro-optimisation, and any performance gain you get to it will be so miniscule it will be difficult to measure it on the nanosecond scale without data centre temperature fluctuations influencing the results. There are far bigger targets for improving performance with the database that dwarf this, and it's likely any performance gains may be eliminated by the cost of the code needed to enforce the schema anyway
    – Tom J Nowell
    Jan 4 at 10:42
  • Thanks for your hint also. Please see the blog linked - IF such an easy optimization, done within 30min (over all table where a userid is linked into), only returns 5% (let alone the results shown in the linked posts, which show even 20% speedup), it's worth a go :D
    – tim
    Jan 4 at 12:28
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/

2
  • Thanks for the insights. I think you are very much right with a lot of your points, even though I found out, because the userid is referenced so often, if I'd change all tables from BIGINT to INT, it'd decrease my DB size by 15%, which is pretty impressive for such a "simple" change, as it's referenced in usermeta and a so called profile-data table in millions of rows. But I agree, I wont do it because it might break plugins as you said, especially because they have the dbDelta() function which might work unexpectedly...
    – tim
    Apr 11 at 6:16
  • But wasting 15% just because of a very !!!useless!!! DB design as a single WP installation will NEVER grow to 4bn users, is just pure insanity. No matter how cheap storage is. Especially because, depending on the hoster you or your customer selected, sometimes 500gb cost 100€ per month on a cloudserver. Not everyone is willing to go to amazon or one of the big US techs :)
    – tim
    Apr 11 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.