WordPress Development Asked by Asifur Rahman on January 2, 2022
This question doesn’t have any codes, it’s more of a theoretical question. I need guidance on what method to look for. I’ve searched on google before but found nothing on this specific case.
I have 2 servers(CentOS) where I have MySQL database set up. In one server(server 1) I have WordPress installation(LAMP) with the database set up. The site is working. However, I want to split the database that is currently on server 1 and host it partially on server 2. Now, I know how to use a remote database in WordPress’ wp-config.php file but here I want to use two databases.
In this case, I can try a manual query from the second server. However the reason I want to split the databases because of a single table that includes more than a million entries. Because the table is also going to be split, and the table is wordpress’ default post meta table, and I wouldn’t know which posts are going to be in which database(more than million entries!), I can’t use a custom query. Also I know that default $wbdp only pulls data from 1 database.
To summarize this question, is there any way to use multiple WordPress Database without having a custom database query?
No. This is not practically possible primarily because there is no single interface to this table where you could implement the functionality you need, and even if you could, it would introduce some extremely gnarly problems.
What you're asking can be simplified to a question about if you could easily shard the wp_postmeta table in Wordpress. (This is relevant because if you could shard a table, it would be a much smaller step to the shards being on multiple databases / servers).
In sharding, some code has to be able to figure out where to look for the result according to some simple algorithm, and then be able to query only that place. For example, if you had 10 million people in a database, you could put them in different tables or different databases split by the first letter of their name*. This means every time the function to get all the information about a person ran, it would first find the right table according to the first letter of their name*.
The reason this is impossible is that there is no single interface to the posts and/or postsmeta table that would allow you to put this code in one place. To achieve what you want, every single place that touched the postmeta table, or did a join to the postmeta table would need altering to figure out which of your two places to look, according to some deterministic algorithm. You'd also have some extreme gnarly problems about figuring out joins (e.g. join across databases/servers is probably possible but sounds like it would quickly defeat any performance gain).
It sounds like you've got a problem and you think this is the solution, but this is almost definitely not a good solution to whatever your problem is (or any problem in a single Wordpress installation). Probably there's a simpler easier solution, or you've got some bad advice/information somewhere.
Why is it a problem that there's 1M rows? Are you just trying to optimise queries to run faster? Are you running out of disk space somewhere? Do you have some assumptions/limitations you didn't add to your question?
[*] Note you'd probably try to shard by something that created roughly equally sized pieces, and you might create some kind of index, but this is just an example, read the Wikipedia page for more info.
Answered by mozboz on January 2, 2022
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP