[wp-hackers] Consolidating database tables and other assorted items

[spencerp]

Matt wrote:
> Yes, but then your changes wouldn't happen immediately, which is what 
> most
> people want. It'll just confuse the average user and then they'll go and
> they'll do the change again, increasing the size of the query. If this 
> were
> to ever happen, which I doubt it's going to anytime soon, we'd also 
> have to
> prioritize the queries. So that when you Publish a post, it won't be 
> posted
> 10 Minutes later, it'll be posted immediately.
>
> Also, just have 1 table for all the Data isn't a good idea for being 
> able to
> sift through it, if need be. The developers may want less tables, but it
> still needs to be Organized and Manageable.
>
> As for the page fading in and out and the Wordpress logo, that seems
> excessive. We should just be working towards making it faster, keeping 
> the
> layout consistent and adding Ajax in places that it makes sense in adding
> Ajax (which is quite a few places).

I'm not a database guru or anything, and though I like Robert Deaton's 
ideas and suggestions. I still have to agree with Matt above though... 
Here's at least one post in the forums regarding software performance 
slowing down (by each newer version):
http://wordpress.org/support/topic/112799?replies=14

--
spencerp
http://spencerp.net


>
>
> On 4/8/07, Robert Deaton <false.hopes at gmail.com> wrote:
>>
>> Over the past few months I've noticed an interest by the devs to
>> consolidate data into as few tables as possible. Take for instance,
>> link_categories, categories, and tags. Now, since these are all
>> clearly exactly the same, its only logical that they go in the same
>> table together, however I propose we take this a step further.
>>
>> Really, logically, all the data we store in the database is the same.
>> Its all WordPress data, that's why I suggest we consolidate all tables
>> into one table, wp_data. This table could have a type column that
>> allows you to choose whether you want
>> posts,postmeta,categories,link_categories,post2cat,options,users,usermeta, 
>>
>> etc.
>>
>> Now, the number of indexes on this table would probably end up being
>> fairly large in order to keep our queries as blindingly efficient as
>> they've always been. This means, unfortunately, that writes to the
>> database would probably take some time because there would be so many
>> indexes. That is why I propose a second table, wp_pending_writes, with
>> a schema
>>
>> CREATE TABLE `wp_pending_writes` (
>> `ID` BIGINT(20)  NOT NULL AUTO_INCREMENT,
>> `pending_query` TEXT  NOT NULL,
>> PRIMARY KEY(`ID`)
>> )
>>
>> where we simply insert all the pending writes. Then, our cron system
>> can run every 10 minutes and go through in the background and actually
>> write all the queries. This way, the server load doesn't spike too
>> horribly with heavy comment traffic and the site continues to load
>> whenever anybody does something that requires a database write. The
>> table would then obviously be cleared.
>>
>> I also suggest we add one more table, for a total of three. This one
>> is mainly because all WordPress development follows WordPress.com
>> these days, and so we regular developers must also be concerned with
>> making sure that each individual WordPress install has information
>> such as blog_id and is efficient enough to scale to hundreds of
>> thousands of blogs. The table I propose is wp_cached_queries, which is
>> a table that has the following structure:
>>
>> CREATE TABLE `wp_cached_queries` (
>> `key` VARCHAR(32)  NOT NULL,
>> `result` TEXT  NOT NULL,
>> PRIMARY KEY(`key`)
>> )
>>
>> This would allow us to cache the results of all the queries as a
>> serialized array into the database, with a key that is the md5 of the
>> actual query that we might run on the wp_data table. This allows us to
>> be incredibly efficient.
>>
>>
>> Now that that's out of the way, there's a few other assorted
>> suggestions that I have. I propose that when navigating in the admin
>> panel, in the spirit of #4089 [http://trac.wordpress.org/ticket/4089],
>> in order to have a consistent and pretty UI, that whenever you click
>> on a link to move to another page in the admin, the entire page fade
>> out to that dark blue WordPress hue with a giant W in a circle in the
>> center of the page, and then fade back in with the contents of the new
>> page. This means that essentially we'd have everything in the admin
>> loaded by fancy javascript and AJAX.
>>
>> Some may see this as a radical proposal, but I think since we have
>> been compelled enough to remove the Firefox and BrowseHappy logos
>> intended for the unpure browser users, then clearly we can go ahead
>> and start implementing radical features that depend on these browsers
>> if we don't have to worry about spreading propaganda to the other
>> users anymore.
>>
>> -- 
>> --Robert Deaton
>> http://lushlab.com
>> _______________________________________________
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>
>
>
>