3

Goal: select 10 most recent rows from posts which are related to a specific user.

There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows).

posts columns:

--------------------------------------------------------------------------------------------
|  id (int)  |  source_id (int)  |  title (varchar)  |  content (longtext)  |  date (int)  |
--------------------------------------------------------------------------------------------

relations columns:

----------------------------------------------------
|  id (int)  |  source_id (int)  |  user_id (int)  |
----------------------------------------------------

I tried using JOIN for retrieving 10 most recent rows from posts which are related to a specific user:

SELECT      p.id, p.title, p.content, r.id AS rid
FROM        posts AS p
JOIN        relations AS r
ON          r.source_id = p.source_id
WHERE       r.user_id = 1
ORDER BY    p.date DESC
LIMIT       10

However, it takes ~30 seconds to execute it (SDD hosting!). I tried lots of index combinations for both tables, including indexes for single and multiple columns – none of that interfered the time of execution anyhow. Is there any way to speed up the selection?

For user_id=1 there are ~1,000 source_id's and ~450,000 rows in posts.

SHOW CREATE TABLE... results:

CREATE TABLE `relations` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) unsigned NOT NULL,
 `source_id` bigint(20) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `source_id` (`source_id`),
 KEY `source_id_2` (`source_id`,`user_id`),
) ENGINE=InnoDB AUTO_INCREMENT=7692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `posts` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `source_id` bigint(20) unsigned NOT NULL,
 `title` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `content` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
 `date` int(10) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `source_id` (`source_id`),
 KEY `date` (`date`),
 KEY `date_2` (`date`,`source_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4867283 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EXPLAIN results:

EXPLAIN results

  • You could try adding a (user_id, source_id) index. – ypercubeᵀᴹ May 4 '16 at 10:15
  • 2
    And (not very relevant to the question) I don't know why you need an id in that table. Will you have 2 or more rows with same user_id and source_id? My guess would be you need only those 2 columns (user_id, source_id) and a UNIQUE constraint on their combination. – ypercubeᵀᴹ May 4 '16 at 10:15
  • @ypercubeᵀᴹ I don't need it in this query, but there are cases where it's compulsory. – Osvaldas May 4 '16 at 10:24
2

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise.

You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then returning the 10 most recent ones. You'll need an index on posts by (date, source_id). It would return quickly for the most recently active users, but take much longer for users without recent posts. Something like the following:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name(u_id int)
BEGIN
  DECLARE fd DATE;
  DECLARE d DATE;

  SELECT MIN(date), MAX(date) INTO fd, d FROM posts;

  CREATE TEMPORARY TABLE last_posts (id int);

  WHILE d > fd AND (SELECT COUNT(*) FROM last_posts) < 10 DO
    INSERT INTO last_posts (id)
    SELECT p.id
    FROM relations AS r
    JOIN posts AS p ON (p.source_id = r.source_id AND
                        p.date > date_sub(d, interval 7 day) AND p.date <= d)
    WHERE r.user_id = u_id
    ORDER BY p.date DESC
    LIMIT 10;

    SET d = date_sub(d, interval 7 day);
  END WHILE;

  SELECT p.id, p.title, p.content, r.id AS rid
  FROM posts p
  JOIN relations AS r ON (r.source_id = p.source_id)
  WHERE p.id IN (SELECT * FROM last_posts)
  ORDER BY p.date DESC
  LIMIT 10;

  DROP TABLE last_posts;
END;;
DELIMITER ;

You could play with the intervals, reducing them to 1 day (more cycles with faster queries). Ensure you have an index on posts (date).

  • Thanks. Any hints how "resort to using a stored procedure, and accumulate results going back in time" would look like? – Osvaldas May 4 '16 at 10:36
  • @Osvaldas I included a sample function – Ziggy Crueltyfree Zeitgeister May 5 '16 at 1:24
  • Haven't used procedures before. Any idea why I get You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER ;; CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name(u_id in' at line 1 error? – Osvaldas May 5 '16 at 7:01
  • 1
    @Osvaldas are you running it using mysql commandline, phpmyadmin or some other client? the delimiter is for client and your client may have some different way of setting it (select box or something instead of the command). – jkavalik May 5 '16 at 19:03
  • 1
    @ZiggyCrueltyfreeZeitgeister thanks for the solution. It may not be perfect because of the dependence on the date column, but probably there's no magic trick and it's all about adapting to data. – Osvaldas May 6 '16 at 8:24
2

This might run faster due to "lazy evaluation". Note that you want to fetch some large columns, yet thousands of rows need to be looked at before deciding which 10 are desired. Instead of gathering all the columns needed, let's get just the PRIMARY KEYs, then reach back into posts only 10 times to get the bulky columns. Note that bulky columns are stored off-record in separate blocks in InnoDB.

(I'll start from @Nicholas's formulation only because it lists the first table first.)

SELECT  p.id, p.title, p.content, rid
    FROM  
      ( SELECT  p.id AS pid, r.id AS rid
            FROM  relations AS r
            JOIN  posts AS p  ON r.source_id = p.source_id
            WHERE  r.user_id = 1
            ORDER BY  p.date DESC
            LIMIT  10 
      ) AS x
    JOIN  posts p  ON p.id = x.pid;

Indexes needed; the order of the columns is important:

relations: INDEX(user_id, source_id, id) -- "covering"
posts:  INDEX(source_id, date, id) -- for the subquery; "covering"
posts:  PRIMARY KEY(id) -- for the outer query; already exists

Side notes:

  • Did you realize that your table default is utf8mb4, but the columns are only utf8? Perhaps an incomplete attempt to convert to utf8mb4? (Needed ALTER TABLE ... CONVERT TO ....)
  • BIGINT is probably overkill.
  • If relations is just a many-to-many mapping, you probably need indexes going both ways. Can you explain the need for id? Recommendations.
-1

I hope I am not reading this wrong but if you want the top 10 most recent posts of a single user wouldn't it be better to run your query on the relations table and join the posts to that query? You would restrict your search to only the posts for the signle user you are attempting to look up then sort the data.

So it would look like this:

SELECT      p.id, p.title, p.content, r.id AS rid
FROM        relations AS r
JOIN        posts AS p
ON          r.source_id = p.source_id
WHERE       r.user_id = 1
ORDER BY    p.date DESC
LIMIT       10
  • 7
    The difference in the JOIN order will not matter at all. The execution plans should be identical. – ypercubeᵀᴹ May 3 '16 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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