Sfrgttk

Manager has noticed coworker's excessive breaks. Should I warn him?

In the Lost in Space intro why was Dr. Smith actor listed as a special guest star?

Why does this quiz question say that protons and electrons do not combine to form neutrons?

What is formjacking?

Do these large-scale, human power-plant-tending robots from the Matrix movies have a name, in-universe or out?

Coworker asking me to not bring cakes due to self control issue. What should I do?

Is there a way to pause a running process on Linux systems and resume later?

Why write a book when there's a movie in my head?

Boss asked me to sign a resignation paper without a date on it along with my new contract

Why is quixotic not Quixotic (a proper adjective)?

What is an explicit bijection in combinatorics?

Badly designed reimbursement form. What does that say about the company?

Can I do anything else with aspersions other than cast them?

What's the function of the word "ли" in the following contexts?

A cancellation property for permutations?

Now...where was I?

Variance of sine and cosine of a random variable

Exploding Numbers

Minimum energy path of a potential energy surface

Build ASCII Podiums

Is the tritone (A4 / d5) still banned in Roman Catholic music?

Can I combine Divination spells with Arcane Eye?

Integral problem. Unsure of the approach.

For the Circle of Spores druid's Halo of Spores feature, is your reaction used regardless of whether the other creature succeeds on the saving throw?

Optimize MySQL update statement

0

I have an update query that selects data from a table (HST) and updates it into another table (PCL). The PCL table contains ~30000 rows while the HST table contains about 1.5 million rows. I have multiple (composite) indexes on HST and despite being a large table all queries on it are fairly quick (~2-3 sec). However, when I attempt to select rows from this table and update PCL, it takes 3-4 hours, and in most cases I get "Lock wait timeout exceeded; try restarting transaction" errors and I have to keep trying until it works.

UPDATE PCL 

SET `T2A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND 

HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.T2 order by HST.`Date` limit 1)    

WHERE `BS` = 'B' AND `T1A` IS NOT NULL; 

Is there a way I can rewrite the above query so that it runs faster?

This is the structure of the PCL table

SrNo, int(6) | Created, datetime | Time, datetime | CNo, varchar(6) | 

Date, date | A1, varchar(4) | A2, varchar(4) | A3, varchar(4) | 

F1, varchar(6) | INST, varchar(20) | BS, char(1) | CAB, char(2) | 

CM, float(8,2) | CMSQL, float(8,2) | T1, float(8,2) | T2, float(8,2) | 

T3, float(8,2) | SL, float(8,2) | FSL, float(8,2) | P1, int(4) | 

P2, int(4) | TT, char(1) | P1C, date | P2C, date | DateP1, date | 

DateP2, date | P1C, date | P2C, date | T1A, date | T2A, date | 

T3A, date | SLH, date | TT, float | TSt, char(3) | TA, float | 

S10K, float | CV, float | R%, float | C1, char(1) | L1, varchar(6) | 

This is the HST table

SrNo, int(11) | SYM, varchar(20) | Date, date | PC, float(8,2) | 

OP, float(8,2) | HP, float(8,2) | LP, float(8,2) | CP, float(8,2) | 

mysql optimization mysql-5.6 update

share

asked 1 min ago

QuarkQuark

1

New contributor

Quark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I have an update query that selects data from a table (HST) and updates it into another table (PCL). The PCL table contains ~30000 rows while the HST table contains about 1.5 million rows. I have multiple (composite) indexes on HST and despite being a large table all queries on it are fairly quick (~2-3 sec). However, when I attempt to select rows from this table and update PCL, it takes 3-4 hours, and in most cases I get "Lock wait timeout exceeded; try restarting transaction" errors and I have to keep trying until it works.

UPDATE PCL 

SET `T2A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND 

HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.T2 order by HST.`Date` limit 1)    

WHERE `BS` = 'B' AND `T1A` IS NOT NULL; 

Is there a way I can rewrite the above query so that it runs faster?

This is the structure of the PCL table

SrNo, int(6) | Created, datetime | Time, datetime | CNo, varchar(6) | 

Date, date | A1, varchar(4) | A2, varchar(4) | A3, varchar(4) | 

F1, varchar(6) | INST, varchar(20) | BS, char(1) | CAB, char(2) | 

CM, float(8,2) | CMSQL, float(8,2) | T1, float(8,2) | T2, float(8,2) | 

T3, float(8,2) | SL, float(8,2) | FSL, float(8,2) | P1, int(4) | 

P2, int(4) | TT, char(1) | P1C, date | P2C, date | DateP1, date | 

DateP2, date | P1C, date | P2C, date | T1A, date | T2A, date | 

T3A, date | SLH, date | TT, float | TSt, char(3) | TA, float | 

S10K, float | CV, float | R%, float | C1, char(1) | L1, varchar(6) | 

This is the HST table

SrNo, int(11) | SYM, varchar(20) | Date, date | PC, float(8,2) | 

OP, float(8,2) | HP, float(8,2) | LP, float(8,2) | CP, float(8,2) | 

mysql optimization mysql-5.6 update

share

asked 1 min ago

QuarkQuark

1

New contributor

Quark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I have an update query that selects data from a table (HST) and updates it into another table (PCL). The PCL table contains ~30000 rows while the HST table contains about 1.5 million rows. I have multiple (composite) indexes on HST and despite being a large table all queries on it are fairly quick (~2-3 sec). However, when I attempt to select rows from this table and update PCL, it takes 3-4 hours, and in most cases I get "Lock wait timeout exceeded; try restarting transaction" errors and I have to keep trying until it works.

UPDATE PCL 

SET `T2A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND 

HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.T2 order by HST.`Date` limit 1)    

WHERE `BS` = 'B' AND `T1A` IS NOT NULL; 

Is there a way I can rewrite the above query so that it runs faster?

This is the structure of the PCL table

SrNo, int(6) | Created, datetime | Time, datetime | CNo, varchar(6) | 

Date, date | A1, varchar(4) | A2, varchar(4) | A3, varchar(4) | 

F1, varchar(6) | INST, varchar(20) | BS, char(1) | CAB, char(2) | 

CM, float(8,2) | CMSQL, float(8,2) | T1, float(8,2) | T2, float(8,2) | 

T3, float(8,2) | SL, float(8,2) | FSL, float(8,2) | P1, int(4) | 

P2, int(4) | TT, char(1) | P1C, date | P2C, date | DateP1, date | 

DateP2, date | P1C, date | P2C, date | T1A, date | T2A, date | 

T3A, date | SLH, date | TT, float | TSt, char(3) | TA, float | 

S10K, float | CV, float | R%, float | C1, char(1) | L1, varchar(6) | 

This is the HST table

SrNo, int(11) | SYM, varchar(20) | Date, date | PC, float(8,2) | 

OP, float(8,2) | HP, float(8,2) | LP, float(8,2) | CP, float(8,2) | 

mysql optimization mysql-5.6 update

share

asked 1 min ago

QuarkQuark

1

New contributor

Quark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

UPDATE PCL 

SET `T2A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND 

HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.T2 order by HST.`Date` limit 1)    

WHERE `BS` = 'B' AND `T1A` IS NOT NULL; 

SrNo, int(6) | Created, datetime | Time, datetime | CNo, varchar(6) | 

Date, date | A1, varchar(4) | A2, varchar(4) | A3, varchar(4) | 

F1, varchar(6) | INST, varchar(20) | BS, char(1) | CAB, char(2) | 

CM, float(8,2) | CMSQL, float(8,2) | T1, float(8,2) | T2, float(8,2) | 

T3, float(8,2) | SL, float(8,2) | FSL, float(8,2) | P1, int(4) | 

P2, int(4) | TT, char(1) | P1C, date | P2C, date | DateP1, date | 

DateP2, date | P1C, date | P2C, date | T1A, date | T2A, date | 

T3A, date | SLH, date | TT, float | TSt, char(3) | TA, float | 

S10K, float | CV, float | R%, float | C1, char(1) | L1, varchar(6) | 

SrNo, int(11) | SYM, varchar(20) | Date, date | PC, float(8,2) | 

OP, float(8,2) | HP, float(8,2) | LP, float(8,2) | CP, float(8,2) | 

share

asked 1 min ago

QuarkQuark

1

New contributor

Quark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 1 min ago

QuarkQuark

1

New contributor

Quark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 1 min ago

QuarkQuark

1

New contributor

Quark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 1 min ago

QuarkQuark

1