I've been editing a script for a company that had this application specifically developed for their business, now they have came to me and wanted some upgrades. The entire application is in PHP and MySQL, minus a couple Python scripts to import 200k records daily to the database. My problem is that I need to allow the ability to categorize and edit notes on each record depending on it's event type. The only way to do this is by the URL embedded within each record, for it's the only truly unique value. Successfully figured this out, but now the page script takes forever (24 secs) to load.
Could someone please assist me in optimizing this bit of code?
$notesq = mysql_query("SELECT * FROM `campaign_event_detail_v2` WHERE `call_recording_url`<>'' AND `event_type_name`='Call'") or die(mysql_error());
while($cnD = mysql_fetch_array($notesq)) {
$callid=$cnD[0];
$getD = mysql_query("SELECT campaign_notes.note, campaign_categories.category FROM campaign_notes LEFT JOIN campaign_categories ON campaign_notes.cid = campaign_categories.cid WHERE campaign_notes.cid='".$cnD['call_recording_url']."' OR campaign_categories.cid='".$cnD['call_recording_url']."'");
$getData = mysql_fetch_row($getD);
mysql_query("UPDATE `campaign_event_detail_v2` SET `note`='".$getData[0]."',`category_id`='".$getData[1]."' WHERE `id`='".$callid."'");
}
Your help is greatly appreciated!
Thanks, J
I think you can probably manage this in a single query:
UPDATE campaign_event_detail_v2 d
LEFT JOIN campaign_notes n ON n.cid = d.call_recording_url
LEFT JOIN campaign_categories c ON c.cid = n.cid
SET d.note = n.note, d.category_id = c.category
WHERE d.call_recording_url != '' AND d.event_type_name = 'Call'
I'm not 100% sure if this is the proper logic, from what I understood it is. I must apologize if it's not. But, my point being: you can probably make it all in a single query.
You probably should add indices on columns like event_type_name, category_id and cid, if they're not already there. It will not affect your scripts, but will take some time to execute depending on how many records you have in your table(s).
Also, it probably would be better to use triggers instead of executing this at each request.