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

[Computer Guru]

Dude.... query prioritization is the least of the worries in such a db design. Please...

Computer Guru
NeoSmart Technologies
http://neosmart.net/blog/

> -----Original Message-----
> From: wp-hackers-bounces at lists.automattic.com [mailto:wp-hackers-
> bounces at lists.automattic.com] On Behalf Of Matt
> Sent: Monday, April 09, 2007 9:19 AM
> To: wp-hackers at lists.automattic.com
> Subject: Re: [wp-hackers] Consolidating database tables and other
> assorted items
> 
> 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).
> 
> 
> 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,userme
> ta,
> > 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
> >
> 
> 
> 
> --
> Matt (speedboxer at gmail.com)
> http://mattsblog.ca/
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers