Mysql EXISTS vs IN slow performance

Question

I have posts and websites (and connecting post_websites). Each post can be on multiple websites, and some websites share the content, so I am trying to access the posts which are attached to particular website IDs.

Most of the cases WHERE IN works fine, but not for all websites, some of them are laggy, and I can't understand a difference.

SELECT *
FROM `posts`
WHERE `posts`.`id` IN (
    SELECT `post_websites`.`post_id`
    FROM `post_websites`
    WHERE `website_id` IN (
        12054,
        19829,
        2258,
        253
    )
) AND
    `status` = 1 AND
    `posts`.`deleted_at` IS NULL
ORDER BY `post_date` DESC
LIMIT 6

Explain

select_type	table	type	key	key_len	ref	rows	Extra
SIMPLE	post_websites	range	post_websites_website_id_index	4	NULL	440	Using index condition; Using temporary; Using filesort; Start temporary
SIMPLE	posts	eq_ref	PRIMARY	4	post_websites.post_id	1	Using where; End temporary

Other version with EXISTS

SELECT *
FROM `posts`
WHERE EXISTS (
    SELECT `post_websites`.`post_id`
    FROM `post_websites`
    WHERE `website_id` IN (
        12054,
        19829,
        2258,
        253
    ) AND
    `posts`.`id` = `post_websites`.`post_id`
) AND
    `status` = 1 AND
    `deleted_at` IS NULL
ORDER BY `post_date` DESC
LIMIT 6

EXPLAIN:

select_type	table	type	key	key_len	ref	rows	Extra
PRIMARY	posts	index	post_date_index	5	NULL	12	Using where
DEPENDENT SUBQUERY	post_websites	ref	post_id_website_id_unique	4	post.id	1	Using where; Using index

Long story short: based on different amounts of posts on each site and amount of websites sharing content the results are different from 20ms to 50s!

Based on the EXPLAIN the EXISTS works better, but on practice when the amount of data in subquery is lower, it can be very slow.

Is there a query I am missing that could work like a charm for all cases? Or should I check something before querying and choose the method of doing so dynamically?

migrations:

CREATE TABLE `posts` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(225) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `post_date` timestamp NULL DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `posts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_at_index` (`created_at`) USING BTREE,
  ADD KEY `status_deleted_at_index` (`status`,`deleted_at`) USING BTREE,
  ADD KEY `post_date_index` (`post_date`) USING BTREE,
  ADD KEY `id_post_date_status_deleted_at` (`id`,`post_date`,`status`,`deleted_at`) USING BTREE;


CREATE TABLE `post_websites` (
  `post_id` int(10) UNSIGNED NOT NULL,
  `website_id` int(10) UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `post_websites`
  ADD PRIMARY KEY (`website_id`, `post_id`),
  ADD UNIQUE KEY `post_id_website_id_unique` (`post_id`,`website_id`),
  ADD KEY `website_id_index` (`website_id`),
  ADD KEY `post_id_index` (`post_id`);

eloquent:

$news = Post::select(['title', 'description'])
            ->where('status', 1)
            ->whereExists(
                function ($query) use ($sites) {
                    $query->select('post_websites.post_id')
                        ->from('post_websites')
                        ->whereIn('websites_id', $sites)
                        ->whereRaw('post_websites.post_id = posts.id');
            })
            ->orderBy('post_date', 'desc');
            ->limit(6)
            ->get();

or 
$q->whereIn('posts.id',
                function ($query) use ($sites) {
                    $query->select('post_websites.post_id')
                        ->from('post_websites')
                        ->whereIn('website_id', $sites);
                });

Thanks.

Answer 1

Many:many table: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table That says to get rid if id (because it slows things down), promote that UNIQUE to be the PK, and add an INDEX in the opposite direction.

Don't use IN ( SELECT ... ). A simple JOIN is probably the best alternative here.

Did some 3rd party package provide those 3 TIMESTAMPs for each table? Are they ever used? Get rid of them.

KEY `id_post_date_status_deleted_at` (`id`,`post_date`,`status`,`deleted_at`) USING BTREE;

is mostly backward. Some rules:

• Don't start an index with the PRIMARY KEY column(s).
• Do start an index with = tests: status,deleted_at