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

[Les Bessant]

Did you mean to post this on the 1st of the month, by any chance?


Les Bessant les at lcb.me.uk
Losing it[1] - http://lcb.me.uk
My flickr pictures - http://flickr.com/photos/lesbessant/


-----Original Message-----
From: wp-hackers-bounces at lists.automattic.com [mailto:wp-hackers-bounces at lists.automattic.com] On Behalf Of Robert Deaton
Sent: 09 April 2007 06:39
To: wp-hackers at lists.automattic.com
Subject: [wp-hackers] Consolidating database tables and other assorted items

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