1
Hi All
I've the following code I need to optimize. Currently tblOutgoing is about 250K registers and growing at a rate of about 20k records per day. This code takes me over 5 secs to run on each iteration. How can I optimize the mysql query in order to make it faster? table tblOutgoing has an unique id slog_id.
if not, how can I add indexes to this table? this table is constantly queried (selects/updates/inserts) based on several field (dst, status and datetime). I read that when a table is updated/inserted constantly, adding indexes might slow/overload mysql. Any advise on this?
Sorry if my question is too basic. I'm really newbie in mysql
[php]for ($i=0 ; $i < count($arr_pool1) ; $i++)
{
$p1_lock = $time_now;
$pool_engine = "pool1-" . $arr_pool1[$i];
// I update an "unknown/random" record from the tblOutgoing
$db_query_p1 = "UPDATE tblOutgoing SET
pool_lock = '$p1_lock',
s_engine = '$dosificador_id',
pool_engine = '$pool_engine',
p_update = '$time_now'
WHERE p_status='10' AND (pool_lock < '$lock_expired' OR ISNULL(pool_lock)) AND ISNULL(s_engine) AND ISNULL(pool_engine) AND p_datetime > $time_recover ORDER BY slog_id LIMIT 1
";
$update = dba_affected_rows($db_query_p1);
// I update an specific record with the id of the previously updated record
$db_update_p1 = "UPDATE plays.s_pool SET
proc_ts = '$time_now',
s_id = (SELECT slog_id from tblOutgoing WHERE pool_engine = '$pool_engine' AND s_engine = '$dosificador_id' ORDER BY slog_id LIMIT 1)
WHERE pool_id = '$arr_pool1[$i]'
";
$db_result_p1 = dba_query($db_update_p1);
} // END FOR[/php]
