Page 1 of 1

Optimize this: Slow MySQL query

Posted: Mon Oct 17, 2005 5:42 am
by Skittlewidth
I've written this query to find all people who specialize in a certain field of treatment which fetches information from four tables.
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 8O ).

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";

Re: Optimize this: Slow MySQL query

Posted: Mon Oct 17, 2005 6:14 am
by Weirdan
First, make sure you have indexes on the following fields:

Code: Select all

KF_Members.member_number 
KF_Address.member_number
KF_Members.status_code
KF_Member_Specialities_Practiced.specialities_codes 
KF_Specialities_codes.autonumber
KF_Specialities_codes.specialities

Posted: Mon Oct 17, 2005 9:07 am
by Skittlewidth
Thanks I'll try that.

I've also looked into doing the larger query a different way using a join to a temporary table. Seems to be working so far!

speed up the query

Posted: Wed Oct 19, 2005 10:06 am
by sebs
I have seen this topic so I am not opening a new one.If you make 6 indexes on 6 columns(1 column each) works faster than 1 index on 6 columns?And if I use a join with a temporary table works faster than to use a query from a single table?Understand?

Posted: Wed Oct 19, 2005 4:43 pm
by timvw
I believe if you use MySQL EXPLAIN you will get an explanation of how the query is solved..

Re: speed up the query

Posted: Thu Oct 20, 2005 3:55 am
by Weirdan
Understand?
well... scarcely.
sebs wrote:If you make 6 indexes on 6 columns(1 column each) works faster than 1 index on 6 columns?And if I use a join with a temporary table works faster than to use a query from a single table?
It all depends on the particular query in question. More indexes mean slower inserts, but usually they make selects faster (if used by optimizer). Full tablescans (read: not using indexes) sometimes faster then using indexes.

There's a whole chapter in MySQL manual dedicated to optimization, and, as I had said previously, reading the manual greatly affects performace of your SQL queries.