Optimize MYSQL (3) Table Join Query

Question

Running an EXPLAIN on some of my query tests have resulted in slow ALL joins even with indexes.
How do I make a MYSQL query with the following information more efficient?

Tables

counter: id (pk), timestamp, user_id (fk)
user: id (PK), username, website_id (fk)
website: id (pk), sitename

SELECT t2.username, t3.sitename, count(*) as views FROM counter t1
LEFT JOIN user t2 ON t2.id = t1.user_id
LEFT JOIN website t3 ON t3.id = t2.website_id
WHERE t1.id <> ""
GROUP BY t1.id
ORDER BY t1.id DESC 

The result in an html table:

username, sitename, counter_views

Answer 1

Don't count(*), instead use count(t1.id)

Question Edit Change

With the question edit, you should put a column name in the count statement of what you want to count, e.g. count(user.id)

I believe the sql is wrong. Don't you want this:

select u.username, s.sitename, count(c.id)
from user u
join website s on u.website_id = s.id
join counter c on u.id = c.user_id
where u.id <> ""
group by u.username, s.sitename
order by u.id desc

Answer 2

Is your data populated yet? If these tables are empty, or very small, the optimizer may be choosing an 'all' query because the whole table is on one page. One page load from disk to get the whole table is faster than hitting a page on disk for the index, and then another page for the real data page.

Answer 3

Your query looks wrong. I would use something like this:

SELECT u.username, w.sitename, c.views
FROM (
  SELECT user_id, COUNT(*) AS views FROM counter GROUP BY user_id
) AS c
LEFT JOIN user u ON u.id = c.user_id
LEFT JOIN website w ON w.id = u.website_id
ORDER BY c.views DESC

I would add index for counter.user_id too.