Users ranking table on how many likes/upvotes users had

I'm trying to create a ranking table based on how many likes/upvotes a user had on all his items in total. User in the upvotes table links to id of the user that made the like, but I think you don't need this.

Hopefully by giving these tables everything will get clear.

I think the trick here is to get all the upvotes by each item and merge them together towards a user this item was from to get a total likes for each user and then rank all the users based on this total. Of course doing this will probably be a slow query so I need a very performant way to handle this.

The hard thing is here mainly that the upvotes table doesn't include the user id.

3 tables:

CREATE TABLE `items` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `user_id` int(255) NOT NULL,
  `img` varchar(500) NOT NULL,
  `message` varchar(200) NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `active` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

CREATE TABLE `upvotes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(255) NOT NULL,
  `item_id` int(255) NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password` binary(60) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `active` int(1) NOT NULL DEFAULT '1',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

I need a performant query giving me the ranking of each user ranked on how many likes they got on all their items?

I managed to write this:

SELECT @rank := @rank + 1 AS rank, m.*
FROM (SELECT 
users.first_name as first_name,
users.last_name as last_name,
count(upvotes.item_id) as total
FROM upvotes
INNER JOIN users
ON users.id = (SELECT items.user_id FROM items WHERE items.id = upvotes.item_id LIMIT 1)
GROUP BY users.id
ORDER BY total DESC
) m, (SELECT @rank := 0) r

But I reckon this will be super slow when the database grows...

Solution

You can do a simple join query in order to get the total likes for each item of user and order your results with the resulting count in descending order

SELECT u.*,i.*,COUNT(DISTINCT up.user) `total_user_likes_item`
FROM users u 
JOIN items i ON(i.user_id = u.id)
JOIN upvotes up ON(up.item_id = i.id)
GROUP BY u.id,i.id
ORDER BY  u.id,i.id,total_user_likes_item DESC

Edit from comments For user total likes you remove i.id from group by as below query

SELECT u.*,COUNT(DISTINCT up.user) `total_user_likes_item`
FROM users u 
JOIN items i ON(i.user_id = u.id)
JOIN upvotes up ON(up.item_id = i.id)
GROUP BY u.id
ORDER BY  total_user_likes_item DESC