I'm not getting any errors as such just a minor performance issue.
我没有收到任何错误,因为这只是一个小问题。
EXPLAIN
SELECT
a.nid,
a.title,
a.uid,
b.parent,
b.weight,
c.name,
d.value
FROM table1 AS a INNER JOIN table2 AS b ON a.vid = b.vid AND a.status = 1
INNER JOIN table3 AS c ON c.uid = a.uid
INNER JOIN table4 AS d ON d.content_id = a.nid AND d.value_type = 'percent' AND d.function = 'average'
When I look at which tables are being referenced, everything is fine, but from table4 where it should only be selecting the "value" field, I'm getting an ALL being called...
当我查看哪些表被引用时,一切都很好,但是从table4开始它应该只选择“value”字段,我得到一个ALL被称为...
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ref PRIMARY,vid,status,uid,node_status_type,nid status 4 const 1
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 databasename.a.vid 1
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 databasename.a.uid 1 Using where
1 SIMPLE d ALL NULL NULL NULL NULL 2 Using where
As you can see, it's selecting * from the final table (d). Why is it doing this when I only need ONE field selected from it? Can anyone help me out?
如您所见,它从最终表(d)中选择*。当我只需要从中选择一个字段时,为什么这样做呢?谁能帮我吗?
3 个解决方案
#1
ALL means all rows, not all columns. Since it says there are no possible keys, I'd guess that you don't have an index on d.content_id or d.value_type or d.function.
ALL表示所有行,而不是所有列。因为它说没有可能的密钥,我猜你没有d.content_id或d.value_type或d.function的索引。
If you wanted to be fancy, you could put an index across all 3 of those columns.
如果你想要花哨,你可以在所有3个列中放置一个索引。
#2
Are d.value_type and d.function indexed fields? That would be initial instinct as to the cause.
d.value_type和d.function是否为索引字段?这将是原因的初步本能。
#3
Add a multi-column index to table4 based on the content_type, value_type and function columns.
根据content_type,value_type和function列向table4添加多列索引。
Your query isn't selecting all the columns from table4, it's selecting all the rows; this isn't much of a problem when there's only two.
您的查询不是从table4中选择所有列,而是选择所有行;当只有两个时,这不是什么大问题。
Note that a MySQL query execution plan might not give the give the answer you expect when you're working with a small number of records; it can be faster for the database to do a full table scan in those circumstances.
请注意,当您处理少量记录时,MySQL查询执行计划可能无法给出您期望的答案;在这种情况下,数据库可以更快地执行全表扫描。
#1
ALL means all rows, not all columns. Since it says there are no possible keys, I'd guess that you don't have an index on d.content_id or d.value_type or d.function.
ALL表示所有行,而不是所有列。因为它说没有可能的密钥,我猜你没有d.content_id或d.value_type或d.function的索引。
If you wanted to be fancy, you could put an index across all 3 of those columns.
如果你想要花哨,你可以在所有3个列中放置一个索引。
#2
Are d.value_type and d.function indexed fields? That would be initial instinct as to the cause.
d.value_type和d.function是否为索引字段?这将是原因的初步本能。
#3
Add a multi-column index to table4 based on the content_type, value_type and function columns.
根据content_type,value_type和function列向table4添加多列索引。
Your query isn't selecting all the columns from table4, it's selecting all the rows; this isn't much of a problem when there's only two.
您的查询不是从table4中选择所有列,而是选择所有行;当只有两个时,这不是什么大问题。
Note that a MySQL query execution plan might not give the give the answer you expect when you're working with a small number of records; it can be faster for the database to do a full table scan in those circumstances.
请注意,当您处理少量记录时,MySQL查询执行计划可能无法给出您期望的答案;在这种情况下,数据库可以更快地执行全表扫描。