Select with limit, get total count, optimize query 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
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.