RE: Help needed with very slow query with ORDER

[David Donachie]

Hi

I am using innodb as well, on MySQL 5.0.6

> -----Original Message-----
> From: JPD [mailto:jpdsoft@stripped]
> Sent: 20 March 2006 16:26
> To: 'David Donachie'
> Subject: RE: Help needed with very slow query with ORDER
> 
> Hello,
> I've got a simular problem with joining tables.
> One of my queries ,joining 5 innodb tables,took more then 15 minutes to
> terminate.
> After changing the table type to myisam, I took a fraction of a second.
> I'm still investigating what could cause the problem.
> Can you tell me what table types you use and what version? (I'm using 5.0)
> 
> Kind regards,
> 
> Met vriendelijke groeten,
> 
> JpdSoft
> > Jean-Pierre Delbarre
> > Sint Walburgastraat 12
> > 9700 Oudenaarde
> >
> > tel: [phone]
> > gsm: 0473/88.26.94
> >  www.jpdsoft.be
> 
> 
> 
> -----Oorspronkelijk bericht-----
> Van: David Donachie [mailto:david.donachie@stripped]
> Verzonden: maandag 20 maart 2006 16:47
> Aan: users-conference@stripped
> Onderwerp: Help needed with very slow query with ORDER
> 
> Hi, I'm new here so I am hoping someone can help me out
> 
> I have a very slow query, which appears to be slow mostly because of my
> order by clause, and I am looking for some way to speed it up. Below is
> all
> the information I can think you might need, please tell me if you need
> more!
> 
> TABLE STRUCTURE
> 
> The query uses the following series of tables, with data being needed from
> each one
> 
> * snp_prefilter
> 
> 	contains two columns, filter_id and voter_id, with filter_id
> 	indexed, which relates a filter id to a voter
> 
> * snp_voter
> 
> 	contains information about a voter, with poll_num indexed, and
> 	a foreign key to snp_voter
> 
> * snp_residence
> 
> 	contains information about a voter's household, and a foreign
> 	key to snp_respolis
> 
> * snp_respolis
> 
> 	contains information about a voter's political data, and has an
> 	index on precinct_number
> 
> * snp_voterresult
> 
> 	contains a row if a voter voted in a given election, with a
> 	value of Y/N or U for voted, and an index on voted
> 
> THE QUERY
> 
> This query returns the first 20 voters matching a certain filter_id who
> have
> not yet had a voting result recorded for the matching election, or who
> have
> no results at all for this election.
> 
> select v.firstname, v.lastname, v.poll_num, v.id AS voter_id,
>        r.zipplus4, r.number, r.num_suffix, r.house_name, r.streetname,
> 
>        r.streettype
>   from snp_prefilter prf
>        left join snp_voter v on (v.id = prf.voter_id)
>        left join snp_residence r on (r.id = v.residence_id)
>        left join snp_respolis rp on (rp.id = r.respolis_id)
>        left join snp_voterresult vr on (vr.voter_id = v.id and
> vr.operation_id = 105)  where prf.filter_id = 105
>    and (vr.voted = 'U' or vr.voted IS NULL)  order by  rp.precinct_number,
> v.poll_num
> 
> This query takes over 55 seconds to run on a set of 100,000 (or so)
> voters.
> The query analysis is
> 
> 1  	SIMPLE  	prf  	ref  	filter_id  	filter_id  	4
> const  	100142  	Using temporary; Using filesort
> 1 	SIMPLE 	v 	eq_ref 	PRIMARY 	PRIMARY 	4
> advokit-dd.prf.voter_id 	1
> 1 	SIMPLE 	r 	eq_ref 	PRIMARY 	PRIMARY 	4
> advokit-dd.v.residence_id 	1
> 1 	SIMPLE 	rp 	eq_ref 	PRIMARY 	PRIMARY 	4
> advokit-dd.r.respolis_id 	1
> 1 	SIMPLE 	vr 	ref 	operation_id 	operation_id 	4
> const 	205 	Using where
> 
> THE PROBLEMS
> 
> The problem seems to be the order by clause, since without it the
> temporary
> and filesort go away, so what I need to know is, is there any way to make
> the sorting more efficient? Both columns sorted on have indexes, but this
> hasn't make the slightest difference.
> 
> Any help very much appreciated!
> 
> David
> 
> 
> --
> MySQL Users Conference Mailing List
> For list archives: http://lists.mysql.com/users-conference
> To unsubscribe:
> http://lists.mysql.com/users-conference?unsub=1
>