Tables and Keys

burnside

Hi, if anyone is brave enough to read this and offer me a few tips, I would appreciate it. Even though it is a drawn out question, I think once your read it you'll get the concept and immediately see what I can do to improve the speed of my queries. Ok, let's go!

I have about 30 companies that have a couple different type of forms submitted on their individual websites. Each time a form is submitted I trigger a phone call to their office so that they can talk with the customer immediately if they are present. If the call is connected, then I will also get more data from what actually transpired during that phone call. The data from the phone calls and forms go to my database. I have created the following tables with these configurations:

COMPANY TABLE
company_id (primary key), contact_name, contact_email, phone_number, address

CONTACT FORM TABLE
contact_form_id (primary key), company_id, visitor_name, email, subject, message, timestamp

SUPPORT FORM TABLE
support_form_id (primary key), company_id, visitor_name, email, problem, operating_system, application, error_message, message, timestamp

PHONE CALL TABLE
phone_call_id (primary key), company_id, form_id, form_type, number_dialed, time_call_started

PHONE CONVERSATION TABLE
phone_conversation_id (primary key), company_id, conversation_to_text, time_call_ended, call_duration

I would like to create a web page that will show a report of all the the phone calls with the phone information and also show the type of form (and it's info) associated with each phone call for that month. Right now I perform a query which joins the COMPANY TABLE with the PHONE CALL TABLE and the PHONE CONVERSATION TABLE. This gives me all the information about the company and phone calls, but now I also need the information from the forms. So what I do is grab the form_id in that first JOIN and during the query fetch loop, I do another query asking for the info from the form table. Here's some rough code to show you what I do:

select company_name, number_dialed, call_duration, conversation_to_text, form_id, form_type where COMPANY_TABLE.company_id = PHONE_CALL_TABLE. company_id AND COMPANY_TABLE. company_id = PHONE_CONVERSATION_TABLE. company_id

then from the fetch, I grab the form_id and form_type and do:

if( form_type == contact form )
select from CONTACT FORM TABLE where form_id = form id
else if( form_type == support form )
select from SUPPORT FORM TABLE where form_id = form id

Ok, done. Any way of optimizing this? I'm using MyISAM as the table type. This allows me to perform full text searches.

Thanks to anyone who can help.
b

select

tomhath

I'd start out by adding phone_call_id to the PHONE_CONVERSATION table. That way you have them linked; now it's too hard to tell which conversations go with which calls. Then everything is linked by ids and you can do it with a single join. The LEFT JOINs allow you to have both CONTACT and SUPPORT in the same query, only one will match the row in PHONE_CALL:

select company_name, number_dialed, call_duration, conversation_to_text, form_id, form_type, ...other columns as needed
from COMPANY
inner join PHONE_CALL
   on COMPANY.company_id = PHONE_CALL.company_id
inner join PHONE_CONVERSATION
   on PHONE_CONVERSATION.phone_call_id = PHONE_CALL.phone_call_id
left join CONTACT_FORM
  on CONTACT_FORM.form_type = 'contact_form' 
   and CONTACT_FORM.contact_form_id = PHONE_CALL.form_id
left join SUPPORT_FORM
  on SUPPORT_FORM.form_type = 'support_form' 
   and SUPPORT_FORM.contact_form_id = PHONE_CALL.form_id

burnside

Hey, that seems to be working pretty nice! The only thing is that I'm trying to pull the email address from the forms and I'm getting the following issue:

When I run the query, I sometimes get two email addresses, other times I get one:

SELECT COMPANY.company_name, PHONE_CONVERSATION.call_duration, PHONE_CONVERSATION.call_to_text, PHONE_CALL.call_source, CONTACT_FORM.email, SUPPORT_FORM.email 
			FROM COMPANY 
                        INNER JOIN PHONE_CALL 
			   on COMPANY.company_id = PHONE_CALL.company_id 
			INNER JOIN PHONE_CONVERSATION 
			   on PHONE_CONVERSATION.phone_id = PHONE_CALL.phone_id 
			LEFT JOIN CONTACT_FORM 
			  ON CONTACT_FORM.contact_id = PHONE_CALL.form_id 
			  AND CONTACT_FORM.company_id = PHONE_CALL.company_id 
			LEFT JOIN SUPPORT_FORM  

			  ON SUPPORT_FORM.support_id = PHONE_CALL.form_id 
			  AND SUPPORT_FORM.company_id = PHONE_CALL.company_id 
			WHERE COMPANY.company_id=1001

How can I distinguish between a contact form email address and a support form email address? I think after that, we're good to go! By the way, this query is running 11 times faster than my previous way, so thanks! I'm definitely going to brush up on my MySQL knowledge!

tomhath

You need to check the PHONE_CALL.form_type = 'contact_form' or 'support_form' in the LEFT JOINs

That's probably why you have duplicates, it's matching a form_id without checking which table the id came from. My first post had the table wrong.