CODEWITHSUNDEEP
phpmysql
rrrfusco
rrrfusco

Reputation: 1119

Optimize MYSQL (3) Table Join Query

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

Upvotes: 1

Views: 475

Answers (3)

Michas
Michas

Reputation: 9468

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.

Upvotes: 0

Hogan
Hogan

Reputation: 70528

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

Upvotes: 2

TheDon
TheDon

Reputation: 388

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.

Upvotes: 1

Related Questions