Re: Table design issue and complex query questions
Posted by: Rick James
Date: August 21, 2012 09:05AM

> if I compare the PartnerType with a constant value (e.g Access Point) then isn't this using the index ?

The optimizer looks at all the available indexes for a table, and picks _one_ to use. Or it decides that none of the indexes are worth using.
When you have
WHERE foo = 123 AND bar = 'xyz'
the optimizer will comtemplate using INDEX(foo) or INDEX(bar). If you have INDEX(foo, bar), that would be even better. If, for whatever reason, it chooses to ignore all your indexes, it will simply test foo and bar as it fetches rows, rejecting rows that don't match the WHERE clause.

Syntax error:
AND cd.dateReference >= p.ProfileEffectiveFrom
AND <= p.ProfileEffectiveTo

Indexes that _may_ be useful:
cd: INDEX(dateReference)
cd: INDEX(CNA_ID, dateReference)
p: INDEX(ProfileActive, CourseID)
p: INDEX(NetworkTypeID, ProfileActive, CourseID)

Put [ code ] and [ /code ] (without spaces) around the EXPLAIN SELECT ...
See http://en.wikipedia.org/wiki/BBCode

A "UNIQUE KEY" is two things:
* An index
* A uniqueness constraint
The 'index' may be useful in a SELECT; the 'constraint' is applied during INSERT and has very little impact on SELECTs.

When using JOINs, please qualify every field ("cd."); it makes it easier for us to know which field is coming from which table.

Options: ReplyQuote


Subject
Written By
Posted
Re: Table design issue and complex query questions
August 21, 2012 09:05AM


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.