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

[Matt]

Well, new features will always affect performance, since they're being
shoved in to a previously slightly optimized database/program. Maybe we
should get a new WP Dev that is great with MySQL optimization, so that every
release won't get slower. Maybe for 2.3 WP should be gone over closely and
Optimized?

On 4/8/07, spencerp <spencerp1 at gmail.com> wrote:
>
> 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
> >>
> >
> >
> >
>
> _______________________________________________
> 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/