Simetrical schreef:
On Tue, Jul 1, 2008 at 11:22 AM, Roan Kattouw roan.kattouw@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)