My understanding is that this would be impossible. Not only that, even if you could move all the data from those tables to the main tables, it won’t reduce the amount of data.
What you’re describing is merging your multisite sites into one site. Which you can do, but then you’re not using Multisite anymore.
@ karpstrucking
Yeah , you’re correct. we are running 5000+ sites using community version. while we try to dump a db( using mysqldump through commandline) it takes too much of time,it’s because the no.of tables.
so thats why they are suggesting us to have one main/common table to reduce the amount of table.
do you have any better solution regarding this dump..
@ Ipstenu.
No, I just wanna merging all the values by having one additional coloumn like “siteid” instead of having separate table .
always i wanna keep multisite only
say for example
wp_options & wp_2_options having the following fields
| Field | Type | Null | Key | Default | Extra |
—————————————————————–
| option_id| bigint(20) unsigned | NO| PRI | NULL | auto_increment |
| option_name | varchar(64) | NO | UNI | |
| option_value | longtext | NO | | NULL |
| autoload | varchar(20) | NO | | yes
———————————————————————-
I wanna merge these two table by their siteid by altering the main table & having one additional filed..
Is it possible?
Thanks,
No.
Again, what you’re describing, putting it all on one table, is called a single site WordPress install.
Replace the term ‘Site ID’ with ‘Category’ and you’ll see why I said that 🙂
So no, you can’t. At least not and still be considering a Multisite.
There are plenty of multisite installs with large numbers of sites that do fine with one database, but if it’s really an issue for you, it is possible to split a multisite network over multiple databases.
http://wpmututorials.com/plugins/shardb/
https://wordpress.org/plugins/shardb/
@ Ipstenu.
Yeah Okay. Is there any way to edit the core of wordpress db & table access to get it done.
First of all do you got my point? i want to redesign tables which are having the same fields by any of its references and i wanna keep mutisite with redesigning a table structure.
@bet Hannon.
Yeah I got your point. But they didn’t accept to have multiple db.. what they want is to reduce table (tables which are having same filed) by any of it’s reference.
Thanks.
FYI , Herewith I have shared the original problem ….
We Coundn’t manage 256000+ tables in a single mysql server. It leads issue in number of open file ids and performance issues.
we are using MYISAM as storage engine, That’s why we are getting no. of files limit problem..
do you have any other better solution to solve this problem ?
Thanks
Your request is odd. WP is designed so that each site has its own set of tables. Putting all site info into one set of tables is possible only if WP was designed that way. Changing the db design would require db and WP core modifications. And if it was designed that way, you might be here complaining about performance or why the tables are so big =)
IMO, you are complaining about the wrong thing. For mysql databases with lots of tables you should be doing mysqldump with different settings. By default mysqldumnp will do a lock on all tables. That is why you are running into the open file id error. You should run mysqldump with the –single-transaction option:
http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html
We Coundn’t manage 256000+ tables in a single mysql server. It leads issue in number of open file ids and performance issues.
Right. Do you really think that there will be a functional difference between 256k tables and a table that’s got 100k entries? Both ways is maddness.
BUT there is a fix!
https://wordpress.org/plugins/hyperdb/
https://wordpress.org/plugins/shardb/
Split the tables across multiple databases 🙂 That’s what WordPress.com does.