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