Revisions to Incredibly long execution time for a update query?

added [query-performance] to 2412 questions - Shog9 (Id=1924)

Link

edited Jan 7, 2020 at 22:23

Community Bot

1

Post Unlocked by Paul White♦

occurred Nov 21, 2019 at 16:26

Notice removed Comments only by Paul White♦

occurred Nov 21, 2019 at 16:26

Mod Moved Comments To Chat

occurred Nov 21, 2019 at 16:23

Post Locked by Paul White♦

occurred Nov 21, 2019 at 16:22

Notice added Comments only by Paul White♦

occurred Nov 21, 2019 at 16:22

Incorporated comments

Source Link

edited Nov 21, 2019 at 16:16

Paul White ♦

90.3k
30
424
663

My server is local, in office, running on a machine in the closet. So, bare metal, not cloud or ESX.

Table        -  invoices
Non_Unique   -  1
Key_Name     -  stateIndex
Seq_in_Index -  1  (Same for all other indexes)
Column_name  -  state
Collation    -  A (all other indexes have an A for this)
Cardinality  - 12 (All other indexes have a Cardinality of 3476)
Sub_part     - Null (Same for all other indexes)
Packed       - Null (Same for all other indexes)
Null         - YES (Same for all other indexes)
Index_type   - BTREE (Same for all other indexes)

Table        -  invoices
Non_Unique   -  1
Key_Name     -  stateIndex
Seq_in_Index -  1  (Same for all other indexes)
Column_name  -  state
Collation    -  A (all other indexes have an A for this)
Cardinality  - 12 (All other indexes have a Cardinality of 3476)
Sub_part     - Null (Same for all other indexes)
Packed       - Null (Same for all other indexes)
Null         - YES (Same for all other indexes)
Index_type   - BTREE (Same for all other indexes)

Table        -  invoices
Non_Unique   -  1
Key_Name     -  stateIndex
Seq_in_Index -  1  (Same for all other indexes)
Column_name  -  state
Collation    -  A (all other indexes have an A for this)
Cardinality  - 12 (All other indexes have a Cardinality of 3476)
Sub_part     - Null (Same for all other indexes)
Packed       - Null (Same for all other indexes)
Null         - YES (Same for all other indexes)
Index_type   - BTREE (Same for all other indexes)

My server is local, in office, running on a machine in the closet. So, bare metal, not cloud or ESX.

Table        -  invoices
Non_Unique   -  1
Key_Name     -  stateIndex
Seq_in_Index -  1  (Same for all other indexes)
Column_name  -  state
Collation    -  A (all other indexes have an A for this)
Cardinality  - 12 (All other indexes have a Cardinality of 3476)
Sub_part     - Null (Same for all other indexes)
Packed       - Null (Same for all other indexes)
Null         - YES (Same for all other indexes)
Index_type   - BTREE (Same for all other indexes)

SHOW INDEX results

Source Link

edited Nov 21, 2019 at 16:11

bjk116

225
2
9

SHOW INDEX ON invoices results:

Table        -  invoices
Non_Unique   -  1
Key_Name     -  stateIndex
Seq_in_Index -  1  (Same for all other indexes)
Column_name  -  state
Collation    -  A (all other indexes have an A for this)
Cardinality  - 12 (All other indexes have a Cardinality of 3476)
Sub_part     - Null (Same for all other indexes)
Packed       - Null (Same for all other indexes)
Null         - YES (Same for all other indexes)
Index_type   - BTREE (Same for all other indexes)

SHOW INDEX ON invoices results:

Table        -  invoices
Non_Unique   -  1
Key_Name     -  stateIndex
Seq_in_Index -  1  (Same for all other indexes)
Column_name  -  state
Collation    -  A (all other indexes have an A for this)
Cardinality  - 12 (All other indexes have a Cardinality of 3476)
Sub_part     - Null (Same for all other indexes)
Packed       - Null (Same for all other indexes)
Null         - YES (Same for all other indexes)
Index_type   - BTREE (Same for all other indexes)

Incorporated comments

Source Link

edited Nov 21, 2019 at 16:02

Paul White ♦

90.3k
30
424
663

Loading

added 91 characters in body

Source Link

edited Nov 21, 2019 at 15:10

bjk116

225
2
9

Loading

added 280 characters in body

Source Link

edited Nov 21, 2019 at 15:04

bjk116

225
2
9

Loading

Added show create statement

Source Link

edited Nov 21, 2019 at 14:49

bjk116

225
2
9

Loading

Source Link

asked Nov 21, 2019 at 14:28

bjk116

225
2
9

Loading

Stack Exchange Network

Return to Question