MySQL Forums
Forum List  »  Performance

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

> Incrementing a date dimension ID by 1 is also incrementing the date by one day.
Fine. IF the translation knows that mapping. (I am assuming you need to fetch stuff across a date range.)

> "daily_sales_fact.sales_date - 7"
-->
DATE_SUB(daily_sales_fact.sales_date, INTERVAL 7 DAYS)

If you are happy with having all dates as INTs, the skip the Dimension table for dates. (At which point, it becomes essentially identical to 'including it as a metric column'.)

The cost of evaluating expressions, assuming they do not prevent INDEX use, can be ignored in performance considerations. The heavy lifting is finding a record.

> "WHERE (largetable.date >= X and largetable.date <= Y)"
If X and Y are constants, it will drill down the BTree to where date=X, then walk forward until it hits Y. If X and Y are fields, there is no decent optimization. Example (of bad case): To see what programs are on now:
WHERE tv_programs.start_time < NOW() AND tv_programs.end_time > NOW()
or, worse,
WHERE tv_programs.start_time < foo.bar AND tv_programs.end_time > foo.bar
These cases are probably what you mean by "only uses the indexed column for half of that range...". Note -- these 'bad' cases involve two different fields.

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.