Optimize this: Slow MySQL query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Optimize this: Slow MySQL query

Post 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";
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Optimize this: Slow MySQL query

Post 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
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post 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!
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

speed up the query

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I believe if you use MySQL EXPLAIN you will get an explanation of how the query is solved..
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: speed up the query

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