Re: Need help optimizing query, awfully slow on only 20000 records
Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html Saqib Ali - http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help optimizing query, awfully slow on only 20000 records
Absolutely! I have multiple indexes. I think it might be a problem with ODBC Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html Saqib Ali - http://www.xml-dev.com - 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]
Need help optimizing query, awfully slow on only 20000 records
Two Questions: Is the same query running directly on the linux server thru mysql is also very slow ? Have you done a explain plan on the query ? Marc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 2 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]
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 2 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 2 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
Re: Need help optimizing query, awfully slow on only 20000 records
Query takes 3.4 seconds to run on the server, but it takes 1-2minutes to run via MyODBC 3.51.06 using passthrough (Access97 is the front end, but it has query type that allows bypass of Access interpretation. Two Questions: Is the same query running directly on the linux server thru mysql is also very slow ? Have you done a explain plan on the query ? Marc. - 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]
RE: Need help optimizing query, awfully slow on only 20000 records
When I send the query throuh comman line, it works perfect 3-4 seconds, but when I do Access97 pass-through query, that is when it runs into 1-2 minutes. It is almost as slow as using Access97 native query that goes through MyODBC, so... 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 2 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 2 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 2 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
RE: Need help optimizing query, awfully slow on only 20000 records
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 2 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 2 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 2 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
RE: Need help optimizing query, awfully slow on only 20000 records
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 2 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 2 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
