Slow Query: Polls table doesn't have an index by userid

[dfabulich]

When I log queries and go to /showuser?id=nufzrftl37o9rw5t (mathbrush), most of the queries are using indexes, but not this one:

select p.pollid, p.title, p.`desc`,
    date_format(p.created, '%M %e, %Y'),
    count(v.gameid) as votecount,
    count(distinct v.gameid) as gamecount
from
    polls as p
    left outer join pollvotes as v on v.pollid = p.pollid
where
    p.userid = 'nufzrftl37o9rw5t'
group by
    p.pollid
order by
    p.created desc;

analyze:

+------+-------------+-------+------+---------------+--------+---------+---------------+------+--------+----------+------------+----------------------------------------------+
| id   | select_type | table | type | possible_keys | key    | key_len | ref           | rows | r_rows | filtered | r_filtered | Extra                                        |
+------+-------------+-------+------+---------------+--------+---------+---------------+------+--------+----------+------------+----------------------------------------------+
|    1 | SIMPLE      | p     | ALL  | NULL          | NULL   | NULL    | NULL          | 511  | 136.00 |   100.00 |     100.00 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | v     | ref  | pollid        | pollid | 34      | ifdb.p.pollid | 30   | 20.40  |   100.00 |     100.00 |                                              |
+------+-------------+-------+------+---------------+--------+---------+---------------+------+--------+----------+------------+----------------------------------------------+

[fredrikr]

Indexes are great for tables with many rows, when you want to find a small subset of those rows. For other cases, they are generally not useful.

Be sure to test the performance before and after adding an index, both with a user who owns many polls and one who owns none. Adding an index may have no effect on performance here, or it may even slow the query down.

[dfabulich]

Good point. The slow query log is empty. I'll file this if it ever turns up as an actually slow query in the log.