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...
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?
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.
If you wanted to be fancy, you could put an index across all 3 of those columns.
Are d.value_type and d.function indexed fields? That would be initial instinct as to the cause.
Add a multi-column index to table4 based on the content_type, value_type and function columns.
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.
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.