------_=_NextPart_001_01C4690F.5A812A6E
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi All,
I have a weekly problem..
Its a table with about 500,000 records, which adds another 30k every
week,=20
after the inserts, I need to do some processing on a status column for
the entire table all over again (For business reasons, because previous
record status may change next week).
=20
The InnoDB table structure is as follows:
=20
logid int(11), - call log id
tag VC(10), - tag
timestamp datetime,
timestamp2 datetime,
flag enum('0','1')
=20
=20
Indexes are Below:
=20
Keyname Type Cardinality Action Field=09
PRIMARY PRIMARY 459405
<http://localhost:8081/phpMyAdmin-2.5...lang=3Den-iso=
-
8859-1&server=3D1&db=3Dd3&table=3D7dayftr&goto=3Dtbl_pr operties_structure=
..php&ba
ck=3Dtbl_properties_structure.php&index=3DPRIMARY>
<http://localhost:8081/phpMyAdmin-2.5...n-iso-8859-1&=
s
erver=3D1&db=3Dd3&table=3D7dayftr&goto=3Dtbl_prope rties_structure.php&bac=
k=3Dtbl_p
roperties_structure.php&sql_query=3DALTER+TABLE+%6 07dayftr%60+DROP+PRIMAR=
Y
+KEY&zero_rows=3DThe+primary+key+has+been+dropped> logid =09
logid INDEX 459405
<http://localhost:8081/phpMyAdmin-2.5...lang=3Den-iso=
-
8859-1&server=3D1&db=3Dd3&table=3D7dayftr&goto=3Dtbl_pr operties_structure=
..php&ba
ck=3Dtbl_properties_structure.php&index=3Dcalllogi d>
<http://localhost:8081/phpMyAdmin-2.5...n-iso-8859-1&=
s
erver=3D1&db=3Dd3&table=3D7dayftr&goto=3Dtbl_prope rties_structure.php&bac=
k=3Dtbl_p
roperties_structure.php&sql_query=3DALTER+TABLE+%6 07dayftr%60+DROP+INDEX+=
%
60calllogid%60&zero_rows=3DIndex+calllogid+has+bee n+dropped> tag =09
timestamp=09
=20
- Every call has a record entry in this table.
- timestamp is the time when the log was made.
- timestamp2 is 168 hours from the timestamp (7 days).
=20
Here is what I require.
- Any tag which has another call on it within 168 hours of a log has a
flag 1, else it is flag 0.=20
- Basically there should not be another call on the same tag within 168
hours (7 days).
=20
Here is what Im doing:
=20
1. inserting raw data into this table.
2. updating timestamp2 as timestamp + 168 hours.
=20
--- all ok so far----
=20
Here is what I am doing:
=20
For each tag i :
loop:
select another logid which has a timestamp between timestamp and
7daytimestamp of (i) and has the same tag has (i)
{
if(exists)
{
update flag=3D1
}
}
loop end:
next tag...
.....500,000 tags.. +30k more every week
=20
it takes me about 6-8 hours to do this.=20
I have tried Enabling/Disabling indexes before the updates, doesnt speed
the process up any.
=20
Please help!!!
=20
Regards,
Amit
=20

------_=_NextPart_001_01C4690F.5A812A6E--

Hi Amit,

I'm wondering about your logic. If I understand what you described
correctly, you're really only working with 2 weeks' worth of data that
actually changes? Or are your transactions really reaching all the way back
through the entire table?

Bob
----- Original Message -----
From: <Amit_Wadhwa (AT) Dell (DOT) com>
To: <java (AT) lists (DOT) mysql.com>
Sent: Tuesday, July 13, 2004 12:26 PM
Subject: Peculiar Problem


Hi All,
I have a weekly problem..
Its a table with about 500,000 records, which adds another 30k every
week,
after the inserts, I need to do some processing on a status column for
the entire table all over again (For business reasons, because previous
record status may change next week).

The InnoDB table structure is as follows:

logid int(11), - call log id
tag VC(10), - tag
timestamp datetime,
timestamp2 datetime,
flag enum('0','1')


Indexes are Below:

Keyname Type Cardinality Action Field
PRIMARY PRIMARY 459405
<http://localhost:8081/phpMyAdmin-2.5...p?lang=en-iso-
8859-1&server=1&db=d3&table=7dayftr&goto=tbl_properties _structure.php&ba
ck=tbl_properties_structure.php&index=PRIMARY>
<http://localhost:8081/phpMyAdmin-2.5...n-iso-8859-1&s
erver=1&db=d3&table=7dayftr&goto=tbl_properties_st ructure.php&back=tbl_p
roperties_structure.php&sql_query=ALTER+TABLE+%607 dayftr%60+DROP+PRIMARY
+KEY&zero_rows=The+primary+key+has+been+dropped> logid
logid INDEX 459405
<http://localhost:8081/phpMyAdmin-2.5...p?lang=en-iso-
8859-1&server=1&db=d3&table=7dayftr&goto=tbl_properties _structure.php&ba
ck=tbl_properties_structure.php&index=calllogid>
<http://localhost:8081/phpMyAdmin-2.5...n-iso-8859-1&s
erver=1&db=d3&table=7dayftr&goto=tbl_properties_st ructure.php&back=tbl_p
roperties_structure.php&sql_query=ALTER+TABLE+%607 dayftr%60+DROP+INDEX+%
60calllogid%60&zero_rows=Index+calllogid+has+been+ dropped> tag
timestamp

- Every call has a record entry in this table.
- timestamp is the time when the log was made.
- timestamp2 is 168 hours from the timestamp (7 days).

Here is what I require.
- Any tag which has another call on it within 168 hours of a log has a
flag 1, else it is flag 0.
- Basically there should not be another call on the same tag within 168
hours (7 days).

Here is what Im doing:

1. inserting raw data into this table.
2. updating timestamp2 as timestamp + 168 hours.

--- all ok so far----

Here is what I am doing:

For each tag i :
loop:
select another logid which has a timestamp between timestamp and
7daytimestamp of (i) and has the same tag has (i)
{
if(exists)
{
update flag=1
}
}
loop end:
next tag...
.....500,000 tags.. +30k more every week

it takes me about 6-8 hours to do this.
I have tried Enabling/Disabling indexes before the updates, doesnt speed
the process up any.

Please help!!!

Regards,
Amit




--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=my...ie.nctu.edu.tw

You are very right, I am working only with 2 Weeks of Data that really
changes,
But I found a workaround,
I made the connection autocommit=false; // since its an InnoDB table,
the queries were autocommitting after each execution ..i assume
And then at the end of all the queries.. Con.commit();
It finishes the entire thing within 2-3 mins..
But is there anything else you would suggest for speeding up?
Id hate to go filtering for the past 2 weeks and change my program each
time I need to do an update on the table.
Thanks for the reply..
Regards,
Amit

-----Original Message-----
From: Robert Dunlop [mailto:rdunlopconsult (AT) earthlink (DOT) net]
Sent: Wednesday, July 14, 2004 1:19 AM
To: Amit_Wadhwa (AT) Dell (DOT) com; java (AT) lists (DOT) mysql.com
Subject: Re: Peculiar Problem


Hi Amit,

I'm wondering about your logic. If I understand what you described
correctly, you're really only working with 2 weeks' worth of data that
actually changes? Or are your transactions really reaching all the way
back through the entire table?

Bob
----- Original Message -----
From: <Amit_Wadhwa (AT) Dell (DOT) com>
To: <java (AT) lists (DOT) mysql.com>
Sent: Tuesday, July 13, 2004 12:26 PM
Subject: Peculiar Problem


Hi All,
I have a weekly problem..
Its a table with about 500,000 records, which adds another 30k every
week, after the inserts, I need to do some processing on a status column
for the entire table all over again (For business reasons, because
previous record status may change next week).

The InnoDB table structure is as follows:

logid int(11), - call log id
tag VC(10), - tag
timestamp datetime,
timestamp2 datetime,
flag enum('0','1')


Indexes are Below:

Keyname Type Cardinality Action Field
PRIMARY PRIMARY 459405
<http://localhost:8081/phpMyAdmin-2.5...p?lang=en-iso-
8859-1&server=1&db=d3&table=7dayftr&goto=tbl_properties _structure.php&ba
ck=tbl_properties_structure.php&index=PRIMARY>
<http://localhost:8081/phpMyAdmin-2.5...n-iso-8859-1&s
erver=1&db=d3&table=7dayftr&goto=tbl_properties_st ructure.php&back=tbl_p
roperties_structure.php&sql_query=ALTER+TABLE+%607 dayftr%60+DROP+PRIMARY
+KEY&zero_rows=The+primary+key+has+been+dropped> logid
logid INDEX 459405
<http://localhost:8081/phpMyAdmin-2.5...p?lang=en-iso-
8859-1&server=1&db=d3&table=7dayftr&goto=tbl_properties _structure.php&ba
ck=tbl_properties_structure.php&index=calllogid>
<http://localhost:8081/phpMyAdmin-2.5...n-iso-8859-1&s
erver=1&db=d3&table=7dayftr&goto=tbl_properties_st ructure.php&back=tbl_p
roperties_structure.php&sql_query=ALTER+TABLE+%607 dayftr%60+DROP+INDEX+%
60calllogid%60&zero_rows=Index+calllogid+has+been+ dropped> tag
timestamp

- Every call has a record entry in this table.
- timestamp is the time when the log was made.
- timestamp2 is 168 hours from the timestamp (7 days).

Here is what I require.
- Any tag which has another call on it within 168 hours of a log has a
flag 1, else it is flag 0.
- Basically there should not be another call on the same tag within 168
hours (7 days).

Here is what Im doing:

1. inserting raw data into this table.
2. updating timestamp2 as timestamp + 168 hours.

--- all ok so far----

Here is what I am doing:

For each tag i :
loop:
select another logid which has a timestamp between timestamp and
7daytimestamp of (i) and has the same tag has (i)
{
if(exists)
{
update flag=1
}
}
loop end:
next tag...
.....500,000 tags.. +30k more every week

it takes me about 6-8 hours to do this.
I have tried Enabling/Disabling indexes before the updates, doesnt speed
the process up any.

Please help!!!

Regards,
Amit




--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:
http://lists.mysql.com/java?unsub=am...hway (DOT) com




--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=my...ie.nctu.edu.tw

Amit,

retrieve the current system date from within your SQL query to determine
where you are in time. Using a server date-based variable in your basic
query should allow you to determine the "window of interest" each time your
process runs. In other words, let the query figure out the current date,
and then process based on that information. Let the server do the work.
You shouldn't need to keep changing your program.
I'm glad to hear what was taking hours before has transformed to
minutes, a decent cost/benefit ratio... Assuming the number of rows you're
processing stays more or less constant, the processing time should as well.
The other answer to your question is: I'm not sure I'm literate enough in
SQL to get your query from minutes to seconds, or less...

Bob

----- Original Message -----
From: "Wadhwa, Amit" <amitwadhwa (AT) hathway (DOT) com>
To: <java (AT) lists (DOT) mysql.com>
Cc: "'Robert Dunlop'" <rdunlopconsult (AT) earthlink (DOT) net>
Sent: Wednesday, July 14, 2004 3:00 AM
Subject: RE: Peculiar Problem




--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=my...ie.nctu.edu.tw