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:[EMAIL PROTECTED] > Sent: Wednesday, August 20, 2003 3:14 PM > To: Michael S. Fischer > Cc: [EMAIL PROTECTED] > 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:[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]
