Need to optimize query

acornejo

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]

Mar 27 '08 #1

ronverdonk

Welcome to The Scripts!

Well, if it is that urgent, show some respect for our community members/experts and enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

Mar 27 '08 #2