MySQL 8 работает очень медленно, когда включает в себя не в пункте «Где где

MySQL 8 работает очень медленно, когда включает в себя не в пункте «Где где ⇐ MySql

1 сообщение • Страница 1 из 1

Anonymous

MySQL 8 работает очень медленно, когда включает в себя не в пункте «Где где

Цитата

Сообщение Anonymous » 19 сен 2025, 20:10

У меня есть веб -сайт, который отображается на главной странице 12 последних загрузок от пользователей сайта. В этом запросе пользователи могут отфильтровать разгрузки от некоторых других пользователей (отсюда и не в пункте). Вот пример запроса: < /p>

Код: Выделить всё

select * from user_uploads
join users on user_uploads.user_id = users.id
join user_settings on users.id = user_settings.user_id
where post_on 
+----+-------------+---------------+------------+--------+--------------------------------------+-------------+---------+--------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table         | partitions | type   | possible_keys                        | key         | key_len | ref                            | rows  | filtered | Extra                                        |
+----+-------------+---------------+------------+--------+--------------------------------------+-------------+---------+--------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | user_settings | NULL       | ALL    | idx_user_id,idx_user                 | NULL        | NULL    | NULL                           | 10853 |    56.77 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | users         | NULL       | eq_ref | PRIMARY,user_status_id,idx_status    | PRIMARY     | 8       | db_name.user_settings.user_id  |     1 |    23.39 | Using where                                  |
|  1 | SIMPLE      | user_uploads  | NULL       | ref    | idx_user_id,idx_posted_on,idx_search | idx_user_id | 8       | db_name.user_settings.user_id  |    23 |    45.00 | Using where                                  |
+----+-------------+---------------+------------+--------+--------------------------------------+-------------+---------+--------------------------------+-------+----------+----------------------------------------------+
< /code>
 edit 2 < /strong>:
Вот создание таблиц для трех задействованных таблиц. < /p>
mysql> show create table users\G
*************************** 1.  row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`email_address` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'no set',
`password` varchar(150) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`referred_by` varchar(45) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`first_name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`last_name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`user_status_id` int unsigned NOT NULL DEFAULT '1',
`biography` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`gender_id` int unsigned NOT NULL DEFAULT '1',
`pronoun_id` int unsigned NOT NULL DEFAULT '3',
`custom_subject` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`custom_object` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`custom_possessive` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`custom_self` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`birthday` date DEFAULT NULL,
`birthday_visibility` enum('Private','Hide Year','Public') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'Private',
`state` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`country_id` int unsigned NOT NULL DEFAULT '1',
`avatar_type` enum('None','System','Image','Gravatar') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'None',
`avatar_shape` enum('square','circle','hexagon') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'square',
`avatar_id` bigint unsigned DEFAULT NULL,
`timezone` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'UTC',
`gallery_views` int unsigned NOT NULL DEFAULT '0',
`featured_tokens` int unsigned NOT NULL DEFAULT '0',
`last_featured_on` date DEFAULT NULL,
`confirmed` tinyint unsigned NOT NULL DEFAULT '0',
`confirm_code` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`lastlogin` datetime DEFAULT NULL,
`lastlogin_ip` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`last_seen` datetime DEFAULT NULL,
`last_seen_ip` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`pw_changed` datetime DEFAULT NULL,
`pw_reset_code` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`user_type_expires_on` date DEFAULT NULL,
`reinstate_on` date DEFAULT NULL,
`delete_on` date DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email_address` (`email_address`),
KEY `user_status_id` (`user_status_id`),
KEY `gender_id` (`gender_id`),
KEY `country_id` (`country_id`),
KEY `confirm_code` (`confirm_code`),
KEY `referred_by` (`referred_by`),
KEY `featured_date` (`last_featured_on`),
KEY `idx_status` (`user_status_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21885 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci COMMENT='User accounts'
< /code>
mysql> show create table user_uploads\G
*************************** 1.  row ***************************
Table: user_uploads
Create Table: CREATE TABLE `user_uploads` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned NOT NULL,
`upload_type_id` int unsigned NOT NULL,
`filename` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`hashed_filename` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
`parsed_document` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`file_sha1` char(40) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
`filesize` int unsigned NOT NULL,
`cover_filename` varchar(255) DEFAULT NULL,
`upload_category_id` int unsigned NOT NULL,
`upload_rating_id` int unsigned NOT NULL,
`qualifier` varchar(6) DEFAULT NULL,
`upload_class_id` int unsigned NOT NULL,
`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`views` int unsigned NOT NULL DEFAULT '0',
`uploaded_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`post_on` datetime DEFAULT NULL,
`allow_comments` tinyint unsigned NOT NULL DEFAULT '1',
`show_comments` enum('Show','Screen','Hide') NOT NULL DEFAULT 'Show',
`comment_type_desired` enum('Any Kind','Casual Only','Light Critiques','Heavy Critiques') NOT NULL DEFAULT 'Any Kind',
`allow_favoriting` tinyint unsigned NOT NULL DEFAULT '1',
`allow_sharing` tinyint unsigned NOT NULL DEFAULT '1',
`hide` tinyint(1) NOT NULL DEFAULT '0',
`queued` tinyint unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_hashed_id` (`hashed_filename`),
KEY `idx_user_id` (`user_id`),
KEY `idx_upload_type` (`upload_type_id`),
KEY `idx_category` (`upload_category_id`),
KEY `idx_rating` (`upload_rating_id`),
KEY `idx_post_on` (`post_on`),
KEY `idx_search` (`post_on`,`hide`,`queued`,`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=354730 DEFAULT CHARSET=latin1
< /code>
mysql> show create table user_settings\G
*************************** 1.  row ***************************
Table: user_settings
Create Table:  CREATE TABLE `user_settings` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned NOT NULL,
`visibility` enum('Public','Registered','Registered-Age') NOT NULL DEFAULT 'Public',
`is_18_plus` tinyint(1) NOT NULL DEFAULT '0',
`show_online_status` tinyint unsigned NOT NULL DEFAULT '1',
`allow_museum_adds` tinyint unsigned NOT NULL DEFAULT '1',
`allow_friend_requests` tinyint unsigned NOT NULL DEFAULT '1',
`allow_user_contact` tinyint unsigned NOT NULL DEFAULT '1',
`allow_add_to_favorites` tinyint unsigned NOT NULL DEFAULT '1',
`show_social_links` tinyint unsigned NOT NULL DEFAULT '1',
`hidden_from_user_directory` tinyint unsigned NOT NULL DEFAULT '0',
`filter_categories` varchar(255) DEFAULT NULL,
`filter_ratings` varchar(255) DEFAULT NULL,
`filter_tags` text,
`show_stats` tinyint unsigned NOT NULL DEFAULT '1',
`show_m_thumbnails` tinyint unsigned NOT NULL DEFAULT '0',
`show_adult_content` tinyint unsigned NOT NULL DEFAULT '0',
`show_signatures` tinyint unsigned NOT NULL DEFAULT '1',
`email_notifications` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_pm` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_comment` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_friend_request` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_mention` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_favorite` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_museum_add` tinyint unsigned NOT NULL DEFAULT '1',
`updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`notify_on_friend_uploads` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_followed_uploads` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_friend_journals` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_followed_journals` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_journal_comment` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_tutorial_comment` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_new_follower` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_group_submission` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_group_member_join` tinyint unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_visibility` (`visibility`),
KEY `idx_is_18_plus` (`is_18_plus`)
) ENGINE=InnoDB AUTO_INCREMENT=42399 DEFAULT CHARSET=latin1
< /code>
Here is the full query as created by the ORM (hence using the simplified query above):
SELECT me.id, me.user_id, me.upload_type_id, me.filename, me.hashed_filename, me.parsed_document, me.file_sha1, me.filesize, me.cover_filename, me.upload_category_id, me.upload_rating_id, me.qualifier, me.upload_class_id, me.title, me.description, me.views, me.uploaded_on, me.post_on, me.allow_comments, me.show_comments, me.comment_type_desired, me.allow_favoriting, me.allow_sharing, me.hide, me.queued, user.id, user.username, user.first_name, user.last_name, user.password, user.birthday, user.birthday_visibility, user.email_address, user.referred_by, user.user_status_id, user.biography, user.gender_id, user.pronoun_id, user.custom_subject, user.custom_object, user.custom_possessive, user.custom_self, user.state, user.country_id, user.avatar_type, user.avatar_shape, user.avatar_id, user.timezone, user.gallery_views, user.featured_tokens, user.last_featured_on, user.confirmed, user.confirm_code, user.lastlogin, user.lastlogin_ip, user.last_seen, user.last_seen_ip, user.pw_changed, user.pw_reset_code, user.user_type_expires_on, user.reinstate_on, user.delete_on, user.created_at, user.updated_at, settings.id, settings.user_id, settings.visibility, settings.is_18_plus, settings.show_online_status, settings.allow_museum_adds, settings.allow_friend_requests, settings.allow_user_contact, settings.allow_add_to_favorites, settings.show_social_links, settings.hidden_from_user_directory, settings.filter_categories, settings.filter_ratings, settings.filter_tags, settings.show_stats, settings.show_m_thumbnails, settings.show_adult_content, settings.show_signatures, settings.email_notifications, settings.notify_on_pm, settings.notify_on_comment, settings.notify_on_friend_request, settings.notify_on_mention, settings.notify_on_favorite, settings.notify_on_museum_add, settings.notify_on_friend_uploads, settings.notify_on_followed_uploads, settings.notify_on_friend_journals, settings.notify_on_followed_journals, settings.notify_on_journal_comment, settings.notify_on_tutorial_comment, settings.notify_on_new_follower, settings.notify_on_group_submission, settings.notify_on_group_member_join,  settings.updated_on
FROM user_uploads me
JOIN users user ON user.id = me.user_id
JOIN user_settings settings ON settings.user_id = user.id
WHERE ( ( post_on 
Note, the execution times for both the full query and the simplified query at the top are nearly identical under the same conditions (with and without the NOT IN clause).
Thanks for the help. 

Подробнее здесь: [url]https://stackoverflow.com/questions/79768612/mysql-8-runs-very-slow-when-including-a-not-in-in-the-where-clause[/url]

1758301835

Anonymous

 У меня есть веб -сайт, который отображается на главной странице 12 последних загрузок от пользователей сайта. В этом запросе пользователи могут отфильтровать разгрузки от некоторых других пользователей (отсюда и не в пункте). Вот пример запроса: < /p>
[code]select * from user_uploads
join users on user_uploads.user_id = users.id
join user_settings on users.id = user_settings.user_id
where post_on 
+----+-------------+---------------+------------+--------+--------------------------------------+-------------+---------+--------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table         | partitions | type   | possible_keys                        | key         | key_len | ref                            | rows  | filtered | Extra                                        |
+----+-------------+---------------+------------+--------+--------------------------------------+-------------+---------+--------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | user_settings | NULL       | ALL    | idx_user_id,idx_user                 | NULL        | NULL    | NULL                           | 10853 |    56.77 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | users         | NULL       | eq_ref | PRIMARY,user_status_id,idx_status    | PRIMARY     | 8       | db_name.user_settings.user_id  |     1 |    23.39 | Using where                                  |
|  1 | SIMPLE      | user_uploads  | NULL       | ref    | idx_user_id,idx_posted_on,idx_search | idx_user_id | 8       | db_name.user_settings.user_id  |    23 |    45.00 | Using where                                  |
+----+-------------+---------------+------------+--------+--------------------------------------+-------------+---------+--------------------------------+-------+----------+----------------------------------------------+
< /code>
 edit 2 < /strong>:
Вот создание таблиц для трех задействованных таблиц. < /p>
mysql> show create table users\G
*************************** 1.  row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`email_address` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'no set',
`password` varchar(150) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`referred_by` varchar(45) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`first_name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`last_name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`user_status_id` int unsigned NOT NULL DEFAULT '1',
`biography` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`gender_id` int unsigned NOT NULL DEFAULT '1',
`pronoun_id` int unsigned NOT NULL DEFAULT '3',
`custom_subject` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`custom_object` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`custom_possessive` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`custom_self` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`birthday` date DEFAULT NULL,
`birthday_visibility` enum('Private','Hide Year','Public') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'Private',
`state` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`country_id` int unsigned NOT NULL DEFAULT '1',
`avatar_type` enum('None','System','Image','Gravatar') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'None',
`avatar_shape` enum('square','circle','hexagon') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'square',
`avatar_id` bigint unsigned DEFAULT NULL,
`timezone` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'UTC',
`gallery_views` int unsigned NOT NULL DEFAULT '0',
`featured_tokens` int unsigned NOT NULL DEFAULT '0',
`last_featured_on` date DEFAULT NULL,
`confirmed` tinyint unsigned NOT NULL DEFAULT '0',
`confirm_code` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`lastlogin` datetime DEFAULT NULL,
`lastlogin_ip` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`last_seen` datetime DEFAULT NULL,
`last_seen_ip` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`pw_changed` datetime DEFAULT NULL,
`pw_reset_code` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`user_type_expires_on` date DEFAULT NULL,
`reinstate_on` date DEFAULT NULL,
`delete_on` date DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email_address` (`email_address`),
KEY `user_status_id` (`user_status_id`),
KEY `gender_id` (`gender_id`),
KEY `country_id` (`country_id`),
KEY `confirm_code` (`confirm_code`),
KEY `referred_by` (`referred_by`),
KEY `featured_date` (`last_featured_on`),
KEY `idx_status` (`user_status_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21885 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci COMMENT='User accounts'
< /code>
mysql> show create table user_uploads\G
*************************** 1.  row ***************************
Table: user_uploads
Create Table: CREATE TABLE `user_uploads` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned NOT NULL,
`upload_type_id` int unsigned NOT NULL,
`filename` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`hashed_filename` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
`parsed_document` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`file_sha1` char(40) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
`filesize` int unsigned NOT NULL,
`cover_filename` varchar(255) DEFAULT NULL,
`upload_category_id` int unsigned NOT NULL,
`upload_rating_id` int unsigned NOT NULL,
`qualifier` varchar(6) DEFAULT NULL,
`upload_class_id` int unsigned NOT NULL,
`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`views` int unsigned NOT NULL DEFAULT '0',
`uploaded_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`post_on` datetime DEFAULT NULL,
`allow_comments` tinyint unsigned NOT NULL DEFAULT '1',
`show_comments` enum('Show','Screen','Hide') NOT NULL DEFAULT 'Show',
`comment_type_desired` enum('Any Kind','Casual Only','Light Critiques','Heavy Critiques') NOT NULL DEFAULT 'Any Kind',
`allow_favoriting` tinyint unsigned NOT NULL DEFAULT '1',
`allow_sharing` tinyint unsigned NOT NULL DEFAULT '1',
`hide` tinyint(1) NOT NULL DEFAULT '0',
`queued` tinyint unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_hashed_id` (`hashed_filename`),
KEY `idx_user_id` (`user_id`),
KEY `idx_upload_type` (`upload_type_id`),
KEY `idx_category` (`upload_category_id`),
KEY `idx_rating` (`upload_rating_id`),
KEY `idx_post_on` (`post_on`),
KEY `idx_search` (`post_on`,`hide`,`queued`,`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=354730 DEFAULT CHARSET=latin1
< /code>
mysql> show create table user_settings\G
*************************** 1.  row ***************************
Table: user_settings
Create Table:  CREATE TABLE `user_settings` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned NOT NULL,
`visibility` enum('Public','Registered','Registered-Age') NOT NULL DEFAULT 'Public',
`is_18_plus` tinyint(1) NOT NULL DEFAULT '0',
`show_online_status` tinyint unsigned NOT NULL DEFAULT '1',
`allow_museum_adds` tinyint unsigned NOT NULL DEFAULT '1',
`allow_friend_requests` tinyint unsigned NOT NULL DEFAULT '1',
`allow_user_contact` tinyint unsigned NOT NULL DEFAULT '1',
`allow_add_to_favorites` tinyint unsigned NOT NULL DEFAULT '1',
`show_social_links` tinyint unsigned NOT NULL DEFAULT '1',
`hidden_from_user_directory` tinyint unsigned NOT NULL DEFAULT '0',
`filter_categories` varchar(255) DEFAULT NULL,
`filter_ratings` varchar(255) DEFAULT NULL,
`filter_tags` text,
`show_stats` tinyint unsigned NOT NULL DEFAULT '1',
`show_m_thumbnails` tinyint unsigned NOT NULL DEFAULT '0',
`show_adult_content` tinyint unsigned NOT NULL DEFAULT '0',
`show_signatures` tinyint unsigned NOT NULL DEFAULT '1',
`email_notifications` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_pm` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_comment` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_friend_request` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_mention` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_favorite` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_museum_add` tinyint unsigned NOT NULL DEFAULT '1',
`updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`notify_on_friend_uploads` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_followed_uploads` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_friend_journals` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_followed_journals` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_journal_comment` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_tutorial_comment` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_new_follower` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_group_submission` tinyint unsigned NOT NULL DEFAULT '1',
`notify_on_group_member_join` tinyint unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_visibility` (`visibility`),
KEY `idx_is_18_plus` (`is_18_plus`)
) ENGINE=InnoDB AUTO_INCREMENT=42399 DEFAULT CHARSET=latin1
< /code>
Here is the full query as created by the ORM (hence using the simplified query above):
SELECT me.id, me.user_id, me.upload_type_id, me.filename, me.hashed_filename, me.parsed_document, me.file_sha1, me.filesize, me.cover_filename, me.upload_category_id, me.upload_rating_id, me.qualifier, me.upload_class_id, me.title, me.description, me.views, me.uploaded_on, me.post_on, me.allow_comments, me.show_comments, me.comment_type_desired, me.allow_favoriting, me.allow_sharing, me.hide, me.queued, user.id, user.username, user.first_name, user.last_name, user.password, user.birthday, user.birthday_visibility, user.email_address, user.referred_by, user.user_status_id, user.biography, user.gender_id, user.pronoun_id, user.custom_subject, user.custom_object, user.custom_possessive, user.custom_self, user.state, user.country_id, user.avatar_type, user.avatar_shape, user.avatar_id, user.timezone, user.gallery_views, user.featured_tokens, user.last_featured_on, user.confirmed, user.confirm_code, user.lastlogin, user.lastlogin_ip, user.last_seen, user.last_seen_ip, user.pw_changed, user.pw_reset_code, user.user_type_expires_on, user.reinstate_on, user.delete_on, user.created_at, user.updated_at, settings.id, settings.user_id, settings.visibility, settings.is_18_plus, settings.show_online_status, settings.allow_museum_adds, settings.allow_friend_requests, settings.allow_user_contact, settings.allow_add_to_favorites, settings.show_social_links, settings.hidden_from_user_directory, settings.filter_categories, settings.filter_ratings, settings.filter_tags, settings.show_stats, settings.show_m_thumbnails, settings.show_adult_content, settings.show_signatures, settings.email_notifications, settings.notify_on_pm, settings.notify_on_comment, settings.notify_on_friend_request, settings.notify_on_mention, settings.notify_on_favorite, settings.notify_on_museum_add, settings.notify_on_friend_uploads, settings.notify_on_followed_uploads, settings.notify_on_friend_journals, settings.notify_on_followed_journals, settings.notify_on_journal_comment, settings.notify_on_tutorial_comment, settings.notify_on_new_follower, settings.notify_on_group_submission, settings.notify_on_group_member_join,  settings.updated_on
FROM user_uploads me
JOIN users user ON user.id = me.user_id
JOIN user_settings settings ON settings.user_id = user.id
WHERE ( ( post_on 
Note, the execution times for both the full query and the simplified query at the top are nearly identical under the same conditions (with and without the NOT IN clause).
Thanks for the help. 

Подробнее здесь: [url]https://stackoverflow.com/questions/79768612/mysql-8-runs-very-slow-when-including-a-not-in-in-the-where-clause[/url]