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:[EMAIL PROTECTED]
> > Sent: Wednesday, August 20, 2003 2:52 PM
> > To: [EMAIL PROTECTED]
> > Cc: [EMAIL PROTECTED]
> > 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:[EMAIL PROTECTED]
> > > > Sent: 19 August 2003 17:29
> > > > To: [EMAIL PROTECTED]
> > > > 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
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> >
> >
>
>
Apolinaras "Apollo" Sinkevicius
Carmel Music & Entertainment, LLC
701 Main Street
Evanston, IL 60202
Phone: (847) 864-5969 X110
Fax: (847) 864-6149
Toll-free: 800-276-5969 X110
e-mail: [EMAIL PROTECTED]
web-site: http://carmelme.com
________________________________
Having an event in Chicago, or would you like to bring Chicago entertainment
to your event? Give Carmel Music & Entertainment a call for the finest
entertainment available in Chicago.
-------------------------------------------------
Visit CARMEL MUSIC & ENTERTAINMENT website http://carmelme.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
