Hello
I have written the below query:
select CONCAT_WS(" ",m.c_salutation, UPPER(left(m.c_firstname,1)),'&',
f.c_salutation, UPPER(left(f.c_firstname,1)), f.c_lastname),
ad1.ad_address1, ad1.ad_address2
from xin_contacts as m,
xin_contacts as f,
em_parent_child_map as m2,
em_parent_child_map as f2,
xin_address as ad1
where m2.pc_parent_id = m.c_id
and f2.pc_parent_id = f.c_id
and f2.pc_parent_id <> m2.pc_parent_id
and f2.pc_student_id = m2.pc_student_id
and m2.pc_correspondance_contact = 'Y'
and f2.pc_correspondance_contact = 'Y'
and (ad1.ad_owners like CONCAT("%","|",m.c_id,"|","%") OR
ad1.ad_owners LIKE CONCAT(m.c_id,"|","%") OR ad1.ad_owners LIKE
CONCAT("%","|",m.c_id))
and upper(m.c_lastname) = upper(f.c_lastname)
GROUP BY 1,2
I have an index on em_parent_child_map.pc_parent_id but when I run an
explain on the query I get this:
+-------+--------+---------------+---------+---------+-----------------+-------+----------------------------------------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+-------+--------+---------------+---------+---------+-----------------+-------+----------------------------------------------+
| m2 | ALL | pc_parent_id | NULL | NULL | NULL
| 9166 | Using where; Using temporary; Using filesort |
| ad1 | ALL | NULL | NULL | NULL | NULL
| 10767 | |
| m | eq_ref | PRIMARY | PRIMARY | 8 | m2.pc_parent_id
| 1 | Using where |
| f2 | ALL | pc_parent_id | NULL | NULL | NULL
| 9166 | Using where |
| f | eq_ref | PRIMARY | PRIMARY | 8 | f2.pc_parent_id
| 1 | Using where |
Why does the query find the key (list it in possible keys) but then
seem to not use it (ie put no entry in the key colum)?
Any help would be appreciated.
Thanks
Brendan