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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
