> 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.