CODEWITHSUNDEEP
mysql
fraxool
fraxool

Reputation: 3229

How to speed up my "friends list" MySQL query?

I'm trying to get a friend list from my database and my query is working but takes lot of time to load the results (sometimes about 10 seconds).

Here it is :

SELECT F.status, U.username, U.email, UI.country, UI.birthday, P.thumb_url
FROM user U, relation F, user_info UI, photo P 
WHERE
CASE
WHEN F.leader = 'USER_ID'
THEN F.subscriber = U.id
WHEN F.subscriber= 'USER_ID'
THEN F.leader= U.id
END
AND
U.id = UI.user_id
AND
UI.main_photo = P.id
AND
F.status='1';

My "relation" table is as follow : 

CREATE TABLE IF NOT EXISTS `relation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `leader` int(11) NOT NULL,
  `subscriber` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT,
  `status` int(11) NOT NULL DEFAULT '0',
  `seen` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

So my question is : do you have some good suggestions in order to improve my query ?

Thanks!

Upvotes: 1

Views: 91

Answers (2)

Schwern
Schwern

Reputation: 164819

As a rule of thumb, if you're using a CASE (or any other if/then logic) in SQL you're probably doing it wrong. SQL is a declarative language, which means you ask it questions. If you're telling it how to answer the question, it'll probably end badly (or slowly). The key is to put the constraining user ID in the WHERE clause and keep it out of the join. That changes it from telling the database how to do its job, to asking the database. The database will take care of the rest.

Using JOIN syntax makes it clearer what's a join and what's a constraint. SQL joins are the thing to use when you want rows from multiple tables stitched together. Databases know how to optimize them.

The second bit is to use EXPLAIN to tell you how MySQL is executing the query, where it's slow. This usually indicates where you need indexes or what part of the query needs to be rewritten.

I'm going to assume that Relationship.leader and Relationship.subscriber are both foreign keys on User.id and describe who is following that user and who they are following, and you want to show every user related to a given USER_ID. There should be no need for the CASE, you can just check if either matches (check me on that one).

SELECT F.status, U.username, U.email, UI.country, UI.birthday, P.thumb_url
FROM user U
JOIN user_info UI
  ON U.id = UI.user_id
JOIN photo P
  ON UI.main_photo = P.id
JOIN relation F
  ON F.leader = U.id OR F.subscriber = U.id
WHERE F.status='1'
  AND U.id = ?

UPDATE: Understanding now that it's supposed to be a list of friends of the user, change U.id = ? to check that user is in the relationship instead.

SELECT F.status, U.username, U.email, UI.country, UI.birthday, P.thumb_url
FROM user U
JOIN user_info UI
  ON U.id = UI.user_id
JOIN photo P
  ON UI.main_photo = P.id
JOIN relation F
  ON F.leader = U.id OR F.subscriber = U.id
WHERE F.status='1'
  AND F.leader = ? OR F.subscriber = ?

We want a list of relationships, but we're using Users as the main table. That's a bit awkward. @fraxool's query which is FROM relation makes more sense, but it could use some clean up about what constraint goes where. JOIN ON is only about meshing tables. WHERE is for constraining what gets returned overall. Applying these principles to @fraxool's new query, we get...

SELECT username, email, ui.gender, ui.country, ui.birthday, p.thumb_url
FROM relation R
JOIN user U
  ON U.id = R.leader OR U.id = R.subscriber
JOIN user_info UI
  ON U.id = UI.user_id
JOIN photo P
  ON UI.main_photo = P.id
WHERE R.status = 1
  AND (R.leader = ? OR R.subscriber = ?)

The additional constraint of U.id != ? is unnecessary unless you can be your own friend. That's something which should be handled in column constraints on relation.leader and relation.subscriber.

Upvotes: 3

fraxool
fraxool

Reputation: 3229

Thanks for your answers.

So I have found a good solution using the JOIN syntax instead of the case. I post it here since it could help some of you.

SELECT username, email, ui.gender, ui.country, ui.birthday, p.thumb_url
FROM relation
RIGHT JOIN user ON
(user.`id` = relation.`leader`
OR
user.`id` = relation.`subscriber`)
AND
user.`id` != '?'
AND
relation.`status` = '1'
JOIN user_info ui, photo p
WHERE
(relation.`leader` = '?'
OR
relation.`subscriber` = '?')
AND user.`id` = ui.`user_id`
AND ui.`main_photo` = p.`id`;

This query takes only around 100ms to load the results whereas it was around 5 seconds for the first one I posted...

Upvotes: 0

Related Questions