2个表数据也不多,但又是加上group by后好慢
SELECT a.goods_id as bb,a.least_num,a.shortInformation,a.short_number,a.advise_num,a.day_sales,a.goods_sn as asn,a.goods_name as aname,a.goods_price,a.goods_cost,a.goods_unit,a.goods_onroad,a.isuse,a.factory,a.goods_suplysn,a.cguser,b.* FROM my_goods AS a JOIN my_onhandle AS b ON a.goods_id = b.goods_id where a.ebay_user='vipyrd' and a.goods_sn = b.goods_sn and b.store_id=18 group by a.goods_sn order by goods_count limit 0,50explain 的结果是:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
type: range
possible_keys: goods_sn_2,goods_sn_3,store_id,goods_sn,goods_id,goods_id_2,goods
_id_3,goods_id_4
key: store_id
key_len: 4
ref: NULL
rows: 21053
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY,ebay_user,goods_sn,goods_sn_2,goods_sn_3,goods_id,goods_s
n_4
key: PRIMARY
key_len: 4
ref: newzehui.b.goods_id
rows: 1
Extra: Using whereshow index如下:
mysql> show index from my_goods\G;
*************************** 1. row ***************************
Table: my_goods
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: my_goods
Non_unique: 1
Key_name: ebay_user
Seq_in_index: 1
Column_name: ebay_user
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn_2
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn_2
Seq_in_index: 2
Column_name: ebay_user
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn_3
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn_3
Seq_in_index: 2
Column_name: ebay_user
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 8. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_id
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 9. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn_4
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
9 rows in set (0.00 sec)mysql> show index from my_onhandle\G;
*************************** 1. row ***************************
Table: my_onhandle
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: my_onhandle
Non_unique: 0
Key_name: goods_sn_2
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: my_onhandle
Non_unique: 0
Key_name: goods_sn_2
Seq_in_index: 2
Column_name: store_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: my_onhandle
Non_unique: 0
Key_name: goods_sn_3
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: ebay_user
Seq_in_index: 1
Column_name: ebay_user
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: store_id
Seq_in_index: 1
Column_name: store_id
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_sn
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 8. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 9. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id
Seq_in_index: 2
Column_name: goods_sn
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 10. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id
Seq_in_index: 3
Column_name: store_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 11. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id_2
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 12. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id_3
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 13. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id_4
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 14. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id_4
Seq_in_index: 2
Column_name: goods_sn
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
14 rows in set (0.00 sec)
SELECT a.goods_id as bb,a.least_num,a.shortInformation,a.short_number,a.advise_num,a.day_sales,a.goods_sn as asn,a.goods_name as aname,a.goods_price,a.goods_cost,a.goods_unit,a.goods_onroad,a.isuse,a.factory,a.goods_suplysn,a.cguser,b.* FROM my_goods AS a JOIN my_onhandle AS b ON a.goods_id = b.goods_id where a.ebay_user='vipyrd' and a.goods_sn = b.goods_sn and b.store_id=18 group by a.goods_sn order by goods_count limit 0,50explain 的结果是:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
type: range
possible_keys: goods_sn_2,goods_sn_3,store_id,goods_sn,goods_id,goods_id_2,goods
_id_3,goods_id_4
key: store_id
key_len: 4
ref: NULL
rows: 21053
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY,ebay_user,goods_sn,goods_sn_2,goods_sn_3,goods_id,goods_s
n_4
key: PRIMARY
key_len: 4
ref: newzehui.b.goods_id
rows: 1
Extra: Using whereshow index如下:
mysql> show index from my_goods\G;
*************************** 1. row ***************************
Table: my_goods
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: my_goods
Non_unique: 1
Key_name: ebay_user
Seq_in_index: 1
Column_name: ebay_user
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn_2
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn_2
Seq_in_index: 2
Column_name: ebay_user
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn_3
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn_3
Seq_in_index: 2
Column_name: ebay_user
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 8. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_id
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 9. row ***************************
Table: my_goods
Non_unique: 1
Key_name: goods_sn_4
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 24125
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
9 rows in set (0.00 sec)mysql> show index from my_onhandle\G;
*************************** 1. row ***************************
Table: my_onhandle
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: my_onhandle
Non_unique: 0
Key_name: goods_sn_2
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: my_onhandle
Non_unique: 0
Key_name: goods_sn_2
Seq_in_index: 2
Column_name: store_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: my_onhandle
Non_unique: 0
Key_name: goods_sn_3
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: ebay_user
Seq_in_index: 1
Column_name: ebay_user
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: store_id
Seq_in_index: 1
Column_name: store_id
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_sn
Seq_in_index: 1
Column_name: goods_sn
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 8. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 9. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id
Seq_in_index: 2
Column_name: goods_sn
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 10. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id
Seq_in_index: 3
Column_name: store_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 11. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id_2
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 12. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id_3
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 13. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id_4
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 14. row ***************************
Table: my_onhandle
Non_unique: 1
Key_name: goods_id_4
Seq_in_index: 2
Column_name: goods_sn
Collation: A
Cardinality: 21056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
14 rows in set (0.00 sec)
解决方案 »
- 数据库ER图完成了,要写成关系图才能创建数据库?
- mysql远程登录问题 如何对外开启3306端口
- varbinary(255) 数据类型,如果创建最优索引
- MySQL query browser如何向表插入数据啊?
- 像主表和子表中同时插入记录如何写insert语句
- 如何把 EXCEL的内容导入mysql?
- mysql的驱动jdbc怎么配置?新手请教,急!!
- MySQL客户端/服务器程序的实现
- 用mm.mysql.jdbc连接mysql的user库,其中的Y/N都能显示出,但localhost不能显示?
- mysql中IN的字段本身就是逗号分隔的怎么写查询语句
- phpMyAdmin打开是空白的
- 一master二slave问题
create index x2 on my_goods(goods_sn,ebay_user);
create index x3 on my_onhandle(goods_sn,store_id);
create index x4 on my_onhandle(store_id,goods_sn);
.goods_id = b.goods_id where a.ebay_user='vipyrd' and a.goods_sn = b.goods_sn and b.store_id=18 group by a.goods_sn order by goods_count limit 0,50
my_goods在goods_id、ebay_user、goods_sn上建立索引
my_onhandle在goods_sn、store_id上建立索引
去掉order by或group by都好快的
show index from ..不要用/G 这个不方便别人分析。