1

The site mimics the "likes" tooltip from Facebook, the only difference is that you can like or dislike the content.

The tooltip shows the first 12 users that rated the content and displays the number of users not shown (i.e. John, Mike, Kim and 24 more rated this content.). If the user clicks on the tooltip, the full list is retrieved via AJAX and displayed on a modal window.

This is my current query for both actions:

SELECT
   mr.*,
   u.name
FROM
   medias_ratings mr
   LEFT JOIN users u ON u.id = mr.user_id
WHERE
   mr.media_id = XXX
   AND mr.id_client = YYY
ORDER BY
   u.name

How can I optimize the above query? The first thing I can think of is having a different query for the limited result. Something like this:

/* Get the first 12 ratings */
SELECT
   mr.*,
   u.name
FROM
   medias_ratings mr
   LEFT JOIN users u ON u.id = mr.user_id
WHERE
   mr.media_id = XXX
   AND mr.id_client = YYY
LIMIT 12

/* Get the total rating count */
SELECT
   COUNT (*)
FROM
   medias_ratings mr
WHERE
   mr.media_id = XXX
   AND mr.id_client = YYY

I've read that running a second query is usually faster than using SQL_CALC_FOUND_ROWS.

Edit 1: The media table already keeps a field for the total likes, so the query is just to fetch the list of users that liked the content.

Edit 2: Table structure, most of the user fields were omitted for brevity

CREATE TABLE `medias_ratings` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_user` int(11) NOT NULL,
 `id_media` int(11) NOT NULL,
 `id_client` int(11) NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `rate` int(1) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user` varchar(100) NOT NULL,
 `pass` varchar(100) NOT NULL,
 `category` int(11) NOT NULL DEFAULT '4',
 `name` varchar(100) DEFAULT NULL,
 `company` varchar(100) DEFAULT NULL,
 `area` varchar(100) DEFAULT NULL,
 `position` varchar(100) DEFAULT NULL,
 `country_code` varchar(3) DEFAULT NULL,
 `email` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `category` (`category`),
 FULLTEXT KEY `SEARCH` (`user`,`name`,`email`)
) ENGINE=MyISAM

closed as too broad by Max Vernon, RolandoMySQLDBA, Philᵀᴹ, Colin 't Hart, RLF Mar 24 '15 at 19:55

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

MyISAM or InnoDB?

Your link does not say anything about SQL_CALC_FOUND_ROWS being slower. And I would expect it to be faster if for no other reason than having one fewer round trip to the server.

The risk comes when you have thousands of rows. Then either method may be spending more time than you can afford. Come back when you encounter that; we can discuss workarounds.

Hmmm... I wonder if the LEFT makes the CALC run faster, since it should not have to reach into users after 12 rows. But I fear that the optimizer is not that smart. I'll guess that the following subquery will avoid that issue:

SELECT  SQL_CALC_FOUND_ROWS mr.*, 
      ( SELECT  name
            FROM  users
            WHERE  id = mr.user_id 
      ) AS name
    FROM  medias_ratings mr
    WHERE  mr.id_media = XXX  -- changed by EDIT
      AND  mr.id_client = YYY
    ORDER BY  mr.`date` DESC
    LIMIT  12;

Now, for indexes.

mr: INDEX(id_media, id_client, date) (Put the '=' columns first, then the ORDER BY.) -- changed by EDIT

users: Add a 'covering' index: INDEX(id, name) specifically to make this query faster -- it can run "Using index", as seen in the EXPLAIN.

  • MyISAM. I've updated the link for the SQL_CALC_FOUND_ROWS issue. I used the left join as you can be anonymous and still use the rating system so some ratings will not have a user. I've updated the question: mr.id is actually mr.media_id which is the id of the content being rated. Finally contents can be active in many sites (clients) that is why I search for a specific client. – Lando Mar 14 '15 at 21:43
  • See my EDIT, especially the compound INDEX. – Rick James Mar 14 '15 at 23:58
  • Thanks! After some benchmarking I can tell you: that the index for the medias_ratings table made all the difference (4x to 5x faster queries); that the join performs better than the subquery (30% to 40% faster); that it is faster (20% to 30%) to make two separated queries than it is to use SQL_CALC_FOUND_ROWS . – Lando Mar 19 '15 at 16:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.