Ygthkb

Buying a "Used" Router

Can I combine Divination spells with Arcane Eye?

Spells that would be effective against a Modern Day army but would NOT destroy a fantasy one

How can I portray body horror and still be sensitive to people with disabilities?

A cancellation property for permutations?

If I have Haste cast on me, does it reduce the casting time for my spells that normally take more than a turn to cast?

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

Found a major flaw in paper from home university – to which I would like to return

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

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

How does the spell Slow affect freefall?

Is it common to refer to someone as "Prof. Dr. [LastName]"?

How to know if I am a 'Real Developer'

Aliased pipeline using head and cut

Can you wish for more wishes from an Efreeti bound to service via an Efreeti Bottle?

Given the mapping a = 1, b = 2, ... z = 26, and an encoded message, count the number of ways it can be decoded

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

What is the reason behind this musical reference to Pinocchio in the Close Encounters main theme?

Is it possible to detect 100% of SQLi with a simple regex?

Is there any danger of my neighbor having my wife's signature?

Log to console in a Lightning Web Component

How does holding onto an active but un-used credit card affect your ability to get a loan?

Was the Soviet N1 really capable of sending 9.6 GB/s of telemetry?

What does @ mean in a hostname in DNS configuration?

Optimize MySQL update statement

find and insert row to another table using mysql triggerHow to optimize indexes on MySQL query with various sortsselect value between particular date and time scheduleBulk Delete for Large Table in MySQLMysql join not workingHow to update 10 million+ rows in MySQL single table as Fast as possible?improve mysql query in large tablesSlow SELECT examining whole tableselect MAX() from MySQL view (2x INNER JOIN) is slowOptimize MySQL query with MAX, GROUP BY, and WHERE

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 5 mins 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 5 mins 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 5 mins 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 5 mins 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 5 mins 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 5 mins 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 5 mins ago

QuarkQuark

1