MySQL join performance not using index

Question

Newcomer warning! Problem with MariaDB (MySQL) v 10.1.26. I have 3 tables: FRIENDS, POSTS, and USERS.

CREATE TABLE USERS(
  uid INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(40) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE, 
  pwhash VARCHAR(200) NOT NULL,
);

CREATE TABLE FRIENDS(
  RID BIGINT AUTO_INCREMENT PRIMARY KEY,
  uid1 INT NOT NULL,                           #user 1 
  uid2 INT NOT NULL,                           #user 2 
  state INT NOT NULL,                          #status from user1 -> user2
  FOREIGN KEY (uid1) REFERENCES USERS(uid),
  FOREIGN KEY (uid2) REFERENCES USERS(uid)
);

CREATE TABLE POSTS(
  pstid BIGINT AUTO_INCREMENT PRIMARY KEY,
  uid INT NOT NULL,
  post_text VARCHAR(500) NOT NULL, 
  time DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(uid) REFERENCES USERS(uid)
);

Also following indexes for the TABLES:

CREATE INDEX users_idx1 ON USERS(username);
CREATE INDEX friends_idx1 ON FRIENDS(uid1);
CREATE INDEX friends_idx2 ON FRIENDS(uid2);
CREATE INDEX posts_idx1 ON POSTS(uid);
CREATE INDEX posts_idx2 ON POSTS(time);

I want to quickly query for the last 100 POSTS made by friends. Current sql:

[EXPLAIN EXTENDED] SELECT * FROM (POSTS as p) 
                   JOIN (FRIENDS as f) ON f.uid2 = p.uid AND 
                                          f.uid1 = ?(userid for current user) 
                                          ORDER BY p.time DESC LIMIT 100;

My assumption was that it MySQL would get all friend uids (f.uid2 where f.uid = currentuserid) via index and then search the posts with that, getting the newest with ORDER BY and limiting the results to 100rows. However when I run EXPLAIN:

 id | select_type | table | type | possible_keys                    | key          | key_len | ref             | rows | filtered | Extra
 1  | SIMPLE      | f     | ref  | friends_idx1, friends_idx2, uid2 | friends_idx1 | 4       | const           |  1   | 100.00   | Using temporary; Using filesort
 1  | SIMPLE      | p     | ref  | posts_idx1                       | posts_idx1   | 4       |  friends.f.uid2 |  1   | 100.00   | --

It says its using filesort and temporary, when p.time,f.uid1, and f.uid2 are indexed. Any help, explaination or suggestions to increase performance are welcome. Thanks in advance!

Answer 1

For this query:

SELECT *
FROM POSTS p JOIN 
     FRIENDS f
     ON f.uid2 = p.uid AND 
        f.uid1 = ?
ORDER BY p.time DESC
LIMIT 100;

The best index for this query is most likely: friends(uid1, uid2) and posts(uid).

Why doesn't MySQL use your indexes? That depends on the table statistics. According to the explain, you have very little data in your tables. Hence, indexes are not going to make a difference. If all the data fits on a single data page, then an index is unlikely to affect performance.