What's wrong with my MySQL query?

Question

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?

Answer 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.

If you wanted to be fancy, you could put an index across all 3 of those columns.

Answer 2

Are d.value_type and d.function indexed fields? That would be initial instinct as to the cause.

Answer 3

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.