List:General Discussion« Previous MessageNext Message »
From:Apollo (Carmel Entertainment) Date:August 20 2003 10:22pm
Subject:RE: Need help optimizing query, awfully slow on only 20000 records
View as plain text  
When I send the query throuh comman line, it works perfect 3-4 seconds, but when
I do Access97 pass-through query, that is when it runs into 1-2 minutes.
It is almost as slow as using Access97 native query that goes through MyODBC, so...

> That doesn't look too bad.  Is the query that slow when you use the
> command-line client alone (connected directly to the server), or is it
> just when accessing the database through the ODBC tunnel?
> 
> --Michael
> 
> > -----Original Message-----
> > From: Apollo (Carmel Entertainment) [mailto:lists@stripped] 
> > Sent: Wednesday, August 20, 2003 3:14 PM
> > To: Michael S. Fischer
> > Cc: mysql@stripped
> > Subject: RE: Need help optimizing query, awfully slow on only 
> > 20000 records
> > 
> > 
> > 
> > Result of EXPLAIN is:
> > table|type|possible_keys|key|key_len|ref|rows|Extra	   
> > inquiries|ALL|contact_id| | | |8253|Using filesort	   
> > individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3|
> > inquiries.indiv_contact_id|1
> > 
> > source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3|
> > inquiries.contact_id|1
> > 	 
> > 
> > 
> > > What does EXPLAIN SELECT <query> show?  Have you read the 
> > chapter in the
> > > manual on optimizing queries?  Do you have all the proper 
> > indices set
> > > up?
> > > 
> > > --Michael
> > > 
> > > > -----Original Message-----
> > > > From: Apollo (Carmel Entertainment) [mailto:lists@stripped] 
> > > > Sent: Wednesday, August 20, 2003 2:52 PM
> > > > To: j.smith@stripped
> > > > Cc: mysql@stripped
> > > > Subject: RE: Need help optimizing query, awfully slow on only 
> > > > 20000 records
> > > > 
> > > > 
> > > > When following query is pulled up, it takes about whole 1-2 
> > > > minutes to come up. "inquiries" table has 17000 records, 
> > > > "contacts" has about 7000, "individual_contacts" has about 
> > > > 16000. It has gotten worse once I upgraded to 4.0 and latest 
> > > > MyODBC. Clients are separate machines (mix of Win98 and 
> > > > WinXP). Those 20K records is what feeds the Access97 form, 
> > > > pull down list filters out some and pulls up about 3K and 
> > > > people just start typing a name and then (since there 
> > > > multiple inquiries for some clients) pull down the list to 
> > > > pick inquiry they want.
> > > > 
> > > > > What are you defining as a huge performance hit?
> > > > > 
> > > > > Is the result set 20K records, or the base tables? How 
> > big are the 
> > > > > base tables?
> > > > > 
> > > > > Are the client and server on the same machine? Pulling 
> > 20K records 
> > > > > across the network could take some time. Formatting 20K 
> > > > records into a 
> > > > > pull -down list in access will also take a long time. 
> > > > Anyway who reads 
> > > > > a 20K list?
> > > > > 
> > > > > Which parts of the process are slow? How does the query 
> > > > perform from 
> > > > > the mysql command line?
> > > > > 
> > > > > > -----Original Message-----
> > > > > > From: Apollo (Carmel Entertainment) 
> > [mailto:lists@stripped]
> > > > > > Sent: 19 August 2003 17:29
> > > > > > To: mysql@stripped
> > > > > > Subject: Need help optimizing query, awfully slow on 
> > only 20000 
> > > > > > records
> > > > > >
> > > > > >
> > > > > > 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0
> > > > > >
> > > > > > I have about 20K records that result from the following 
> > > > query. Front 
> > > > > > end for the database is ACCESS97 and pulling up 20K 
> > > > records makes a 
> > > > > > huge performance hit.
> > > > > > For the form in question I am using PASSTHROUGH type query
> > > > > > (the one that just
> > > > > > passes everything straight to server without ODBC).
> > > > > > NOTE: souce_for_inquiries_form is the join table and is
> > > > > > searchable in the from
> > > > > > (it feeds a pull-down list).
> > > > > >
> > > > > > SELECT inquiries.inquiry_id, inquiries.contact_id, 
> > > > > > inquiries.indiv_contact_id, inquiries.phone, inquiries.fax,
> 
> > > > > > inquiries.agent_id, inquiries.inquiry_date,
> > > > > > inquiries.event_type, inquiries.letter_type, 
> > inquiries.event_date,
> > > > > > inquiries.event_date_general, inquiries.event_location,
> > > > > > inquiries.guests,
> > > > > > inquiries.hours, inquiries.budget, 
> > inquiries.event_description,
> > > > > > inquiries.talent_description, inquiries.past_use,
> > > > > > inquiries.referred_by,
> > > > > > inquiries.date_sent, inquiries.end_user,
> inquiries.event_id,
> > > > > > inquiries.notes,
> > > > > > source_for_inquiries_form.organization,
> > > > > > source_for_inquiries_form.mailing_address_1,
> > > > > > source_for_inquiries_form.mailing_address_2,
> > > > > > source_for_inquiries_form.city,
> > > > > > source_for_inquiries_form.state, 
> > source_for_inquiries_form.zip,
> > > > > > source_for_inquiries_form.contact_type,
> > > > > > individual_contacts.contact_name_first,
> > > > > > individual_contacts.contact_name_last,
> > > > > > individual_contacts.contact_prefix,
> > > > > > individual_contacts.contact_title,
> individual_contacts.email
> > > > > > FROM inquiries
> > > > > > LEFT JOIN individual_contacts ON inquiries.indiv_contact_id
> =
> > > > > > individual_contacts.indiv_contact_id
> > > > > > INNER JOIN contacts AS source_for_inquiries_form ON
> > > > > > inquiries.contact_id =
> > > > > > source_for_inquiries_form.contact_id
> > > > > > ORDER BY  inquiries.inquiry_id DESC;
> > > > > >
> > > > > > -------------------------------------------------


-------------------------------------------------
Visit CARMEL MUSIC & ENTERTAINMENT website http://carmelme.com
Thread
Need help optimizing query, awfully slow on only 20000 recordsCarmel Entertainment)19 Aug
  • RE: Need help optimizing query, awfully slow on only 20000 recordsJim Smith20 Aug
RE: Need help optimizing query, awfully slow on only 20000 recordsCarmel Entertainment)20 Aug
  • RE: Need help optimizing query, awfully slow on only 20000 recordsMichael S. Fischer21 Aug
RE: Need help optimizing query, awfully slow on only 20000 recordsCarmel Entertainment)21 Aug
  • RE: Need help optimizing query, awfully slow on only 20000 recordsMichael S. Fischer21 Aug
  • Seeking advice on best table structureScott Haneda21 Aug
    • Re: Seeking advice on best table structurePete McNeil21 Aug
    • Re: Seeking advice on best table structureRoger Baklund21 Aug
      • Re: Seeking advice on best table structureRajesh Kumar21 Aug
        • Re: Seeking advice on best table structureRoger Baklund21 Aug
Re: Need help optimizing query, awfully slow on only 20000 recordsCarmel Entertainment)21 Aug
RE: Need help optimizing query, awfully slow on only 20000 recordsCarmel Entertainment)21 Aug
  • MySQL 4.0.13 Memory problem under heavy loadKayra Otaner21 Aug
    • RE: MySQL 4.0.13 Memory problem under heavy loadMichael S. Fischer21 Aug
    • Re: MySQL 4.0.13 Memory problem under heavy loadDan Nelson21 Aug
  • Re: MySQL 4.0.13 Memory problem under heavy loadMartin Gainty21 Aug