最近空间商老说我们MYSQL的时间超过限制时间。
限制时间为20分钟。他们提供了慢查询的log文件给我。
我找了花费时间的SQL来explain.log文件记录着
### 1707 Queries
### Total time: 5486.213481, Average time: 3.21395048681898
### Taking 0.247792 to 6.894097 seconds to complete
### Rows analyzed 646275 Rows_affected: 0 Rows_read: 646275 - 965716 Rows_affected: 0 Rows_read: 29580explain出来的记录:id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p2fmd ref PRIMARY,products_id,fit_model,language_id fit_model 257 const 18 Using where
1 SIMPLE p2c ref PRIMARY,categories_id PRIMARY 4 article.p2fmd.products_id 65 Using index
1 SIMPLE c eq_ref PRIMARY,categories_status PRIMARY 4 article.p2c.categories_id 1 Using where
全部都用到索引,而且搜索出来的速度都在0.1秒甚至0.001秒内,但不知道为什么还会被记录为慢查询里面去。。我很疑惑究竟什么时候才会被记录到慢查询?求解释~~
限制时间为20分钟。他们提供了慢查询的log文件给我。
我找了花费时间的SQL来explain.log文件记录着
### 1707 Queries
### Total time: 5486.213481, Average time: 3.21395048681898
### Taking 0.247792 to 6.894097 seconds to complete
### Rows analyzed 646275 Rows_affected: 0 Rows_read: 646275 - 965716 Rows_affected: 0 Rows_read: 29580explain出来的记录:id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p2fmd ref PRIMARY,products_id,fit_model,language_id fit_model 257 const 18 Using where
1 SIMPLE p2c ref PRIMARY,categories_id PRIMARY 4 article.p2fmd.products_id 65 Using index
1 SIMPLE c eq_ref PRIMARY,categories_status PRIMARY 4 article.p2c.categories_id 1 Using where
全部都用到索引,而且搜索出来的速度都在0.1秒甚至0.001秒内,但不知道为什么还会被记录为慢查询里面去。。我很疑惑究竟什么时候才会被记录到慢查询?求解释~~
解决方案 »
- MYSQL 怎么模糊搜索啊。咋我实现不了的
- mysql所有的数据库后缀为*.MYD大小都为0,怎么回事?
- 求助~关于中文变量为什么不能存进mysql里
- 按日期统计的问题
- mysql安装几天后,无法连接到server,好几次了
- mysql驱动与中文编码的问题
- MySQL现在最新版支持中文的全文索引(中文的FULL TEXT)吗?
- 某字段 z 的取值只有 0和1,如该字段为1 则变为 0 ,如果该字段为0 则变为1,请问:如何做到?
- 如何转换查询结果数据????????
- 求这个项目中的mysql服务器应该如何进行安全配置。
- skip-SLAVE-START在mysql5.5是不是取消了
- MYSQL 全文索引安装失败。
FROM products_to_fit_model_description p2fmd, categories c, products_to_categories p2c
WHERE p2fmd.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND c.categories_status =1
AND p2fmd.fit_model_id != '87098'
AND p2fmd.fit_model = 'nokia n78'
AND p2fmd.language_id = '3'
show index from p2fmd
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
products_to_fit_model_description 0 PRIMARY 1 fit_model_id A NULL NULL NULL BTREE
products_to_fit_model_description 0 PRIMARY 2 language_id A 1127672 NULL NULL BTREE
products_to_fit_model_description 1 products_id 1 products_id A 5125 NULL NULL BTREE
products_to_fit_model_description 1 fit_model 1 fit_model A 80548 NULL NULL BTREE
products_to_fit_model_description 1 language_id 1 language_id A 8 NULL NULL BTREE show index from p2c
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
products_to_categories 0 PRIMARY 1 products_id A NULL NULL NULL BTREE
products_to_categories 0 PRIMARY 2 categories_id A 6507 NULL NULL BTREE
products_to_categories 1 categories_id 1 categories_id A 929 NULL NULL BTREE show index from categories
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
categories 0 PRIMARY 1 categories_id A 925 NULL NULL BTREE
categories 1 idx_categories_parent_id 1 parent_id A 42 NULL NULL BTREE
categories 1 categories_status 1 categories_status A 1 NULL NULL BTREE
全部提供~
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
products_to_fit_model_description 0 PRIMARY 1 fit_model_id A NULL NULL NULL BTREE
products_to_fit_model_description 0 PRIMARY 2 language_id A 1127672 NULL NULL BTREE
products_to_fit_model_description 1 products_id 1 products_id A 5125 NULL NULL BTREE
products_to_fit_model_description 1 fit_model 1 fit_model A 80548 NULL NULL BTREE
products_to_fit_model_description 1 language_id 1 language_id A 8 NULL NULL BTREE
show index from p2c
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
products_to_categories 0 PRIMARY 1 products_id A NULL NULL NULL BTREE
products_to_categories 0 PRIMARY 2 categories_id A 6507 NULL NULL BTREE
products_to_categories 1 categories_id 1 categories_id A 929 NULL NULL BTREE
show index from categories
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
categories 0 PRIMARY 1 categories_id A 925 NULL NULL BTREE
categories 1 idx_categories_parent_id 1 parent_id A 42 NULL NULL BTREE
categories 1 categories_status 1 categories_status A 1 NULL NULL BTREE
这个时间是什么
查询也会锁表吗?
CPU的很正常,就MYSQL的时间去到170多分钟。不过里面有一段SQL我很纠结的。。就是like %%的操作。不能用索引,但又不能不使用。。