List:General Discussion« Previous MessageNext Message »
From:Apollo (Carmel Entertainment) Date:August 20 2003 9:51pm
Subject:RE: Need help optimizing query, awfully slow on only 20000 records
View as plain text  
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