472,102 Members | 1,088 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,102 software developers and data experts.

Need to optimize query

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
1 1684
ronverdonk
4,258 Expert 4TB
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Andreas Falck | last post: by
6 posts views Thread by Bruce D | last post: by
5 posts views Thread by xeqister | last post: by
3 posts views Thread by Reddy | last post: by
4 posts views Thread by Huaer.XC | last post: by
13 posts views Thread by Frank Swarbrick | last post: by
4 posts views Thread by Hemant Shah | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.