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

[Computer Guru]

Thank you Robert!

I don't know why people don't seem to get it: TABLES ARE CHEAP!
Database normalization almost always equals more tables - but hundred-fold flexibility.

It may take a couple of KB more, but the speed-ups certainly are worth it.

But this raises one *real* problem that I've been pondering about for a while: though the WP db is well spread about, it's either very incorrectly done or db access is very incorrectly coded.

Why does a clean WP install take 16 queries to load something as simple as the homepage?
It takes 20 queries to display a post.... it's illogical, and given WP's DB structure, wholly unnecessary.

A query for the post contents and metadata (such as the category it's in, the status of the post, the date it was published, etc.), a query for comments, a query for the current logged-in user, AND????

I'm sure I missed one or two important things, but how a post + comments = 20 queries on a stock WP *without a single plugin* is beyond me.

The thing is - since the WP data is well distributed and properly stored (the WP db-structure is fairly well done, with the exception of foreign keys IMO) why does it take so many queries to do so little?

I think if WP ever gets a database abstraction layer - even if it's something as heavy as MDB2/PearDB - it'll perform *better* since a hell of a lot of queries will be consolidated.

I've made these observations before, and I'm probably going to make them again.... I honestly believe WP will never utilize the DB correctly because no one uses the WP API where possible and instead issue their own DB commands. It's sad, and I only wish I had the time needed to just consolidate all queries in dbqueries.php and make WP use that _and only that_ to access core features. But I don't have the time, and doing that will break WP.... Plus *gasp* it'll make WP perform good.

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 Robert Deaton
> Sent: Monday, April 09, 2007 8:39 AM
> 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,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