I have two MySQL tables, named stockin and stockout. Now I would like to check two table for an unique column named serialno to find stock-in hand rows. I have tried 4 select query line to random look-ups between two table. But, Every query taking too much time to execute. Can you check table indexes of tables for any wrong issues? If you have any correction or suggestion, i will appreciate.
我有两个MySQL表,名为stockin和stockout。现在,我想检查两个表,找到一个名为serialno的唯一列,以查找库存手中的行。我已经尝试了4个选择查询行到两个表之间的随机查找。但是,每个查询都需要花费太多时间来执行。你可以检查表的表索引是否有任何错误的问题?如果您有任何更正或建议,我将不胜感激。
mysql> SHOW INDEX FROM stockin\G
*************************** 1. row ***************************
Table: stockin
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: stockin_id
Collation: A
Cardinality: 14657
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: stockin
Non_unique: 1
Key_name: shiptype
Seq_in_index: 1
Column_name: shiptype
Collation: A
Cardinality: 18
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: stockin
Non_unique: 1
Key_name: userid
Seq_in_index: 1
Column_name: userid
Collation: A
Cardinality: 22
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: stockin
Non_unique: 1
Key_name: uom
Seq_in_index: 1
Column_name: uom
Collation: A
Cardinality: 10
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: stockin
Non_unique: 1
Key_name: warehouseid
Seq_in_index: 1
Column_name: warehouse_id
Collation: A
Cardinality: 6
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: stockin
Non_unique: 1
Key_name: project_id_for_stockin
Seq_in_index: 1
Column_name: project_id
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: stockin
Non_unique: 1
Key_name: cus_id
Seq_in_index: 1
Column_name: cus_id
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
7 rows in set (0.01 sec)
and
mysql> SHOW INDEX FROM stockout\G
*************************** 1. row ***************************
Table: stockout
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: stockout_id
Collation: A
Cardinality: 19654
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: stockout
Non_unique: 1
Key_name: eng_id
Seq_in_index: 1
Column_name: eng_id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: stockout
Non_unique: 1
Key_name: project_id
Seq_in_index: 1
Column_name: project_id
Collation: A
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: stockout
Non_unique: 1
Key_name: warehouseid
Seq_in_index: 1
Column_name: warehouseid
Collation: A
Cardinality: 47
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: stockout
Non_unique: 1
Key_name: response_type_id
Seq_in_index: 1
Column_name: response_type_id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: stockout
Non_unique: 1
Key_name: cus_id
Seq_in_index: 1
Column_name: cus_id
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: stockout
Non_unique: 1
Key_name: employee_id
Seq_in_index: 1
Column_name: employee_id
Collation: A
Cardinality: 19
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 8. row ***************************
Table: stockout
Non_unique: 1
Key_name: userid
Seq_in_index: 1
Column_name: userid
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 9. row ***************************
Table: stockout
Non_unique: 1
Key_name: uom
Seq_in_index: 1
Column_name: uom
Collation: A
Cardinality: 11
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
9 rows in set (0.02 sec)
Thanks
1 个解决方案
#1
0
I have solved the random look-ups between two table to add index which unique columns are in random checking. I have added column in index like following code, and immediately. I was amazed with the results execution time. That query was taking more than 3.30 minutes, when i added index it takes only 0.03 seconds.
我已经解决了两个表之间的随机查找以添加索引,其中唯一列是随机检查的。我已经在索引中添加了列,如下面的代码,并立即。我对结果执行时间感到惊讶。该查询耗时超过3.30分钟,当我添加索引时只需0.03秒。
CREATE INDEX columnname ON tablename(columnname)
#1
0
I have solved the random look-ups between two table to add index which unique columns are in random checking. I have added column in index like following code, and immediately. I was amazed with the results execution time. That query was taking more than 3.30 minutes, when i added index it takes only 0.03 seconds.
我已经解决了两个表之间的随机查找以添加索引,其中唯一列是随机检查的。我已经在索引中添加了列,如下面的代码,并立即。我对结果执行时间感到惊讶。该查询耗时超过3.30分钟,当我添加索引时只需0.03秒。
CREATE INDEX columnname ON tablename(columnname)