[wp-testers] Wordpress scaling problems

[Paul Robinson]

I'd just like to chip in here and say bravo to Otto. In one fell swoop you
have just explained to me how WP does a lot of things with it'd DB & why you
shouldn't use the category, post name combo for your permalink. You should
be a teacher. :P

Paul.

2009/1/29 Otto <otto at ottodestruct.com>

> On Thu, Jan 29, 2009 at 5:43 AM, matthijs <matthijsenator at gmail.com>
> wrote:
> > Again, I understand that in the - current - wordpress design this is how
> it
> > goes. But if you forget the current design for a moment, and think about
> how
> > you could otherwise design a system handling permalinks. I can hardly
> > believe there is no other way to do this. How are all those other
> websites
> > with more then a few hundred pages doing it then?
>
> Actually, I think this deserves a bit more discussion... Let's
> consider a permalink like %category%/%postname%.
>
> So you're handed a URL like /mycat/mypost. You start by parsing it
> into mycat and mypost. You don't know what these are. They're just
> strings to you. So, first, you have to consider what "mycat" is.
>
> First, you query to see if "mycat" is a pagename. This is a select
> from wp_posts where post_slug = mycat and post_type = page. No joy
> there.
>
> Next, you query to see if "mycat" is a category. This is a select from
> wp_terms join wp_term_taxonomy on (term_id = term_id) where term =
> mycat and taxonomy = category. Hey, we found a mycat, so that's good.
> Unfortunately, this just tells us that it's a category, which is
> rather useless in retrieving the actual post we're looking for. So we
> ignore the category.
>
> Now, we move on to the "mypost". Again, we start querying:
> 1. Is it a page?  select from wp_posts where post_slug = mypost and
> post_type = page. Nope.
> 2. Is it a category?  select from wp_terms join wp_term_taxonomy on
> (term_id = term_id) where term = mypost and taxonomy = category. Nope.
> 3. Is it a post? select from wp_posts where post_slug = mypost and
> post_type = post. Bingo.
>
> The whole goal is to determine the specific post being asked for. The
> category is not helpful in this respect, and we have to do a couple
> queries just to figure out that we need to ignore it. Five queries to
> determine what the post is with this structure. Five queries, two of
> them expensive (joins ain't cheap). And these have to happen on every
> load of a post on your site.
>
> With the pre-built list of rewrite rules, we already have the data in
> memory, in a big array. All that need be done with the current case is
> an array lookup on the url. And the query to get that list was one
> simple one, just get the rewrite_rules option. In fact, most all the
> options are preloaded with one big query at the beginning load anyway,
> so it's a no-query operation.
>
> The only reason you're running 2400 queries is because your
> rewrite_rules are not being saved in the first place. Now, I grant you
> that 2400 queries is a heck of a lot to build the rewrite rule list in
> the first place, and no doubt some optimization can be done there, but
> if that data was saved properly when you built the rewrite rules, then
> the pre-calculation saves time and queries and makes it much faster.
> The only reason it's not working is that the data isn't being saved.
>
> It's certainly possible to conceive of an alternate strategy. What if
> we stored all the rewrite rules in a separate table? Now we can pull
> from that table as needed, and each row is quite small by comparison.
> This would certainly work for this specific case, but it makes other
> cases require more queries. Since now we don't have them in memory any
> more, we have to pull a new row for each different rewrite case.
>
> There's no particularly good all-around solution, unless you want to
> precalculate every possible URL and store them in a separate table
> with that URL as the index. Then it's one query for all cases, but
> maintaining that table becomes difficult and problematic. It also
> becomes static, without the ability to adapt to new cases. Perhaps
> this is the way to go for the future, perhaps a plugin could make this
> sort of optimization by overriding the rewrite system. But it seems
> like more trouble than it's worth, really.
>
> -Otto
> _______________________________________________
> wp-testers mailing list
> wp-testers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-testers
>