Clever Geek Handbook

What's wrong with my MySQL query?

I don't get any errors per se, only minor performance issues.

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 I only need to select the "value" field, I get a call to 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 selects * from the final table (d). Why does this happen when I only need one field selected from it? Can anyone help me?

+1


source to share


3 answers


ALL

means all rows, not all columns. Since it says there are no possible keys, I would assume you don't have an index on d.content_id or d.value_type or d.function.



If you want to be fancy, you can put an index on all three of these columns.

+6


source


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



+2


source


Add a multi-column index to the column table4

-based content_type

, value_type

and function

.

Your query does not select all columns from table4

, it selects all rows; it's not a big problem when there are only two.

Note that the MySQL query plan may not provide the answer you expect when working with a small number of records; it may be faster for the database to perform a full table scan under these circumstances.

+1


source






More articles:


All Articles