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:
(user_id, source_id)
index. – ypercubeᵀᴹ May 4 '16 at 10:15id
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