Simetrical schreef:
On Tue, Jul 1, 2008 at 11:22 AM, Roan Kattouw
<roan.kattouw(a)home.nl> wrote:
You wouldn't need to go through the page
table here if you added a
cat_page field to the category table.
Categories are not guaranteed to have associated pages, so we must
keep cat_title. In that case cat_page is redundant and denormalized,
and should only be added if there's some specific performance benefit
to it, which there's not for any application I've heard.
I wasn't suggesting ditching cat_title. The fact that cat_page is
sometimes 0 doesn't really matter, since categories without
corresponding pages can't be redirects, so they don't have a redirect
target (which is what the query was about). I believe there actually is
a performance benefit to introducing cat_page, explained below.
Can be done on cat_title=cl_to too, doesn't
really matter. Joining on
cat_id is cleaner of course, but I don't think it'll be any faster.
Joining on integers is considerably faster than joining on VARCHARs.
In InnoDB, joining on primary keys is considerably faster than joining
on anything else. These shouldn't be neglected, in general.
I had the idea
joining on integers would be faster, I guess I
underestimated how much. I didn't know about the primary key thing, but
it makes sense (it's called *primary* key for a reason).
However,
the speed of the join is not the limiting factor here, the problem is
you'll have to filesort the entire category.
With the schema I was backing,
yes. However, schema #1 doesn't eliminate
the need to check for a category's redirect target when adding a page to
it, and since joining on cat_page=page_id is faster than joining on
cat_title=page_title (because of the int vs. varchar and primary vs.
non-primary issues), that would constitute a "specific performance
benefit" for adding cat_page, wouldn't it?
Roan Kattouw (Catrope)