56,677
社区成员
发帖
与我相关
我的任务
分享sql执行效率
SELECT e.equipment_id
FROM EQUIPMENT e
WHERE e.customer_id IN(
SELECT c.customer_id
FROM CUSTOMER c
WHERE FIND_IN_SET(c.customer_id, queryTreeCustId(117))) ;
上面这条sql，单独执行里面的耗时比执行整条sql要长，为什么呢？
FROM EQUIPMENT e
WHERE e.customer_id IN(
SELECT c.customer_id
FROM CUSTOMER c
WHERE FIND_IN_SET(c.customer_id, queryTreeCustId(117))) ;
上面这条sql，单独执行里面的耗时比执行整条sql要长，为什么呢？
...全文
请发表友善的回复…
发表回复
huqingwei0824 2015-07-09
- 打赏
- 举报
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>未知表</title>
<meta name="GENERATOR" content="HeidiSQL 9.1.0.4913">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
thead tr {background-color: ActiveCaption; color: CaptionText;}
th, td {vertical-align: top; font-family: "Tahoma", Arial, Helvetica, sans-serif; font-size: 8pt; padding: 3px; }
table, td {border: 1px solid silver;}
table {border-collapse: collapse;}
thead .col0 {width: 42px;}
.col0 {text-align: right;}
thead .col1 {width: 84px;}
thead .col2 {width: 52px;}
thead .col3 {width: 61px;}
thead .col4 {width: 94px;}
thead .col5 {width: 147px;}
thead .col6 {width: 65px;}
thead .col7 {width: 67px;}
thead .col8 {width: 54px;}
.col8 {text-align: right;}
thead .col9 {width: 156px;}
</style>
</head>
<body>
<table caption="未知表 (1 rows)">
<thead>
<tr>
<th class="col0">id</th>
<th class="col1">select_type</th>
<th class="col2">table</th>
<th class="col3">type</th>
<th class="col4">possible_keys</th>
<th class="col5">key</th>
<th class="col6">key_len</th>
<th class="col7">ref</th>
<th class="col8">rows</th>
<th class="col9">Extra</th>
</tr>
</thead>
<tbody>
<tr>
<td class="col0">1</td>
<td class="col1">SIMPLE</td>
<td class="col2">c</td>
<td class="col3">index</td>
<td class="col4"></td>
<td class="col5">IDX_CUSTOMER_LEVEL</td>
<td class="col6">303</td>
<td class="col7"></td>
<td class="col8">177</td>
<td class="col9">Using where; Using index</td>
</tr>
</tbody>
</table>
<p>
<em>generated 2015-07-09 19:15:55 by <a href="http://www.heidisql.com/">HeidiSQL 9.1.0.4913</a></em>
</p>
</body>
</html>
huqingwei0824 2015-07-09
- 打赏
- 举报
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" "e" "ALL" \N \N \N \N "126" "Using where"
"1" "SIMPLE" "c" "eq_ref" "PRIMARY" "PRIMARY" "4" "zzxwifi_db_v2.e.customer_id" "1" "Using where; Using index"
rick-he 2015-07-08
- 打赏
- 举报
mysql子查询有可能用不了索引，
你explain下
ACMAIN_CHM 2015-07-07
- 打赏
- 举报
分别贴出 explain select ... 以供分析。
