Re: Table design issue and complex query questions
Posted by: Rick James
Date: August 19, 2012 01:28PM

> AND DATE_FORMAT(p.YearMonth,'%Y-%m') BETWEEN '2012-07' AND '2012-08'
That will not be able to use an index on YearMonth. Instead, phrase it this way:
AND p.YearMonth >= '2012-07-01'
AND p.YearMonth < '2012-09-01'

> KEY `PartnerType` (`PartnerType`)
INDEXes on just a ENUM are almost always useless. What SELECT(s) do you think would use it?

> UNIQUE KEY `ccf_id` (`ccf_id`),
> UNIQUE KEY `CNA_ID_2` (`CNA_ID`),
> UNIQUE KEY `CNA_ID` (`CNA_ID`,`startDatetime`)
That's an odd combination of UNIQUE keys. That's 3 constraints that are checked on every INSERT (or UPDATE). The last one adds no extra constraint, but may add a useful index.

Please provide the EXPLAIN SELECT for the query.

Options: ReplyQuote


Subject
Written By
Posted
Re: Table design issue and complex query questions
August 19, 2012 01:28PM


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.