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.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.id = XXX
AND mr.id_client = YYY
LIMIT 12
/* Get the total rating count */
SELECT
COUNT (*)
FROM
medias_ratings mr
WHERE
mr.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`)
)
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`)
)
Aucun commentaire:
Enregistrer un commentaire