MySQL Forums
Forum List  »  Performance

Re: Optimizing a query so that the smaller table is handled first
Posted by: Rick James
Date: April 05, 2011 09:16PM

Do NOT create a dimension table for any 'continuous' metric! Yeah, it works fine for fetching one date. But when you want a date range, performance plummets.

But it is smaller. A DATE is 3 bytes. What did you use for the id? A SMALLINT? That's 2 bytes. A tiny savings for a big headache. So, saving space is a wimpy argument.

But it lets me dedup things (a key point in "normalization"). A date is not an entity, it is an attribute. If you change your wedding date, does that change every other event that happens on that date? No. So that reason for normalizing is bogus.

But I am looking for items "sold on Tuesdays". Well, guess what. It is faster (as Jørgen points out) to do a table scan than have any kind of index on day of week, even in the Fact table. Day-of-month is another matter. But is that a realistic case? I contend that it is not worth the overhead.

Ditto for "length", "weight", etc. There, you usually want a range. Turning a range into a set of ids hurts.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.