473,698 Members | 2,503 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need to optimize query

1 New Member
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_pool 1) ; $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_i d',
pool_engine = '$pool_engine',
p_update = '$time_now'
WHERE p_status='10' AND (pool_lock < '$lock_expired' OR ISNULL(pool_loc k)) AND ISNULL(s_engine ) AND ISNULL(pool_eng ine) AND p_datetime > $time_recover ORDER BY slog_id LIMIT 1
";
$update = dba_affected_ro ws($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_i d' ORDER BY slog_id LIMIT 1)
WHERE pool_id = '$arr_pool1[$i]'
";
$db_result_p1 = dba_query($db_u pdate_p1);
} // END FOR[/php]
Mar 27 '08 #1
1 1769
ronverdonk
4,258 Recognized Expert Specialist
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

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

Similar topics

0
1795
by: Andreas Falck | last post by:
Hi, I ran the code below on two different versions, 4.0.14 and 4.0.16 respectively, both running RH 7.3 on intel. In version mysql server version 4.0.14 the SELECT privelege suffices for "optimize table", but (seemingly) the server version 4.0.16 requires INSERT as well. Is the INSERT privelege necessary for performing optimize in mysql
6
2122
by: Bruce D | last post by:
Could someone please help to explain why the following query isn't using the index... explain select id from kbm where state = 'MA' table type possible_keys key key_len ref rows Extra kbm ALL State NULL NULL NULL 1000000 Using where The field 'State' is Char(2). It has an index.
3
2783
by: gizmo | last post by:
I have a stored procedure that queries a database using a Select statement with some inner joins and conditions. With over 9 million records it takes 1 min 36 sec to complete. This is too slow for my requirements. Is there any way I can optimize this query. I have thought about using an indexed view. I haven't done one before, does anyone know if this would have potential to improve performance or indeed any other performance...
5
1782
by: xeqister | last post by:
Greetings all, We have a complicated statement in DB2 which takes long hour to complete and we have created most of the indexes. Does anybody knows how to tune the following statement to optimize the query? "SELECT AL3.LAB, AL3.DEPARTMENT, AL6.NAME, Count (AL4.ITEM), AL5.SALES_TERRIOTARY, AL1.DATE_SERVICE FROM YR2005.REQUESTX AL1, YR2005.RESULTP AL2, YR2005.PANEL AL3, YR2005.RESULTV AL4, YR2005.DOCTOR AL5, YR2005.DEPT AL6 WHERE...
3
2821
by: Reddy | last post by:
The sql query for my datagrid returns 100, 000 records. But the datagrid should display 20 records per page. I am using datagrid paging, but it is taking too much time for the page to load. Is there any way I can optimize the speed. Any sample code would be great. Thanks, Reddy
4
2027
by: Huaer.XC | last post by:
>From the following MySQL command: EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON t3.name = t1.name WHERE t1.id IN(123, 124); which result is: ------------------------------------------------------------------------------------- table type possible_key key key_len ref rows Extra t1 const PK, name PK 4 const 10 t3 const PK PK 4 const 10
13
3118
by: Frank Swarbrick | last post by:
IBM has a product for the VSE operating system called the VSAM Redirector. It allows you to use VSAM to access RDBMS tables/views as if they were actual VSAM files. We're doing a comparison right now between Oracle, which we've been running in production for many years, and DB2, which we are just starting with. One of the tests I am trying is to see how efficient the VSAM Redirector works with DB2 versus Oracle. Below are two types of...
4
1857
by: Hemant Shah | last post by:
Folks, I am having problem with an application that uses static SQL, the application basically browses through the table given start and end key most of the time it is processed from begining to end. The db2expln tells me that there is no Data or Index prefetch. I am running DB2 UDB 8 on AIX 5.3, and I am using DMS tablespace on raw logical volumes on a SAN.
3
2497
zabsmarty
by: zabsmarty | last post by:
Can any one help me to make my query code optimize and load faster. Please help me or any example what steps should we use to optimize. Thank You
0
8609
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9169
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9030
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8871
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6528
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4622
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3052
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2335
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2007
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.