KF_Members contains the name and qualification status of an individual
KF_Specialities_codes is a table of speciality names and a unique id for each.
KF_Member_Specialities_Practiced is a table of Member_ids with a number corresponding to the Speciality Codes table. A Member will appear in a new row for every additional speciality practiced. There are examples where a single member will appear more than 30 times.
KF_Address contains address details, as well as an important field declaring whether a user is happy for his/her details to be displayed on the website. Members can appear multiple times in this table for various clinics/ home addresses.
I've written this query which at the moment only fetches the surname, member_id and speciality name of anyone specialising in a particular treatment, displaying 10 results at a time (combined with PHP the offset increments by 10). The trouble is, even with this stripped down version its horribly slow to cycle through the results, and I will eventually need to display full addresses, clinic names and all specialities associated with a member (I can't get my head around that in one query
Is there anyway I can optimise the following query?
Thanks
Code: Select all
select distinct
KF_Members.surname,
KF_Members.member_number,
KF_Specialities_codes.specialities
from
KF_Members,
KF_Specialities_codes,
KF_Member_Specialities_Practiced,
KF_Address
where
KF_Members.member_number = KF_Address.member_number
and
KF_Members.status_code
in
(1,2,4,9)
and
KF_Member_Specialities_Practiced.specialities_codes
=
KF_Specialities_codes.autonumber
and
KF_Specialities_codes.specialities = 'Anxiety'
and
KF_Address.public_details = true
order by
KF_Members.member_number
limit $offset, 10";