[SELECT name AS keyword, CONCAT('/search/?find=', name) AS keywordLink FROM stories_keywords WHERE id IN (SELECT keyword_id FROM stories_keywords_to_stories WHERE story_id IN (SELECT stories.id FROM stories LEFT JOIN stories_local ON (stories_local.story_id = stories.id AND stories_local.language = 'en') LEFT JOIN videos_encoded ON (videos_encoded.story_id = stories.id AND videos_encoded.profile_id = 3) LEFT JOIN aliases ON (aliases.content_type = 'story' AND aliases.content_id = stories.id AND aliases.state = 1) LEFT JOIN ( SELECT stories_local.story_id AS story_id, IF( -- LOCATE(' caucasian ', stories_local.name) > 0 -- OR -- LOCATE(' caucasian ', stories_local.text_description_for_search) > 0, -- 1 = 1, exact_phrase_matches.story_id IS NOT NULL, 10, IF( -- (LOCATE(' caucasian ', stories_local.name) > 0) -- OR -- (LOCATE(' caucasian ', stories_local.text_description_for_search) > 0), -- 1 = 1, all_words_matches.story_id IS NOT NULL, 20, IF( /*(SELECT COUNT(story_kwords.id) FROM stories_keywords_to_stories AS story_kwords LEFT JOIN stories_keywords AS kwords ON kwords.id = story_kwords.keyword_id WHERE story_kwords.story_id = stories_local.story_id AND kwords.language = 'en' AND (kwords.name = 'caucasian') GROUP BY story_kwords.story_id HAVING COUNT(story_kwords.id) = 1) IS NOT NULL,*/ -- 1 = 1, all_k_words_count.story_id IS NOT NULL, 30, IF( /*(SELECT COUNT(story_kwords.id) FROM stories_keywords_to_stories AS story_kwords LEFT JOIN stories_keywords AS kwords ON kwords.id = story_kwords.keyword_id WHERE story_kwords.story_id = stories_local.story_id AND kwords.language = 'en' AND (kwords.name = 'caucasian') GROUP BY story_kwords.story_id) IS NOT NULL,*/ -- 1 = 1, some_k_words_count.n IS NOT NULL, 40, 667 ) ) ) ) AS matching_score FROM stories_local -- exact_phrase_matches.story_id IS NOT NULL for stories with exact full phrase match in name or description LEFT JOIN -- (SELECT 666 AS story_id) (SELECT story_id FROM stories_local WHERE language = 'en' AND ( LOCATE(' caucasian ', stories_local.name) > 0 OR LOCATE(' caucasian ', stories_local.text_description_for_search) > 0 ) ) AS exact_phrase_matches ON exact_phrase_matches.story_id = stories_local.story_id -- all_words_matches.story_id IS NOT NULL for stories with all words exactly found in name or description LEFT JOIN -- (SELECT 666 AS story_id) (SELECT story_id FROM stories_local WHERE language = 'en' AND ( (LOCATE(' caucasian ', stories_local.name) > 0) OR (LOCATE(' caucasian ', stories_local.text_description_for_search) > 0) ) ) AS all_words_matches ON all_words_matches.story_id = stories_local.story_id -- all_k_words_count.n (as well as .story_id) IS NOT NULL when ALL keywords are exactly found in a story LEFT JOIN (SELECT COUNT(story_kwords.id) AS n, story_kwords.story_id AS story_id FROM stories_keywords_to_stories AS story_kwords LEFT JOIN stories_keywords AS kwords ON kwords.id = story_kwords.keyword_id WHERE kwords.language = 'en' AND (kwords.name = 'caucasian') GROUP BY story_kwords.story_id HAVING COUNT(story_kwords.id) = 1) AS all_k_words_count ON all_k_words_count.story_id = stories_local.story_id -- some_k_words_count.n IS NOT NULL when AT LEAST ONE keyword is exactly found in a story LEFT JOIN (SELECT COUNT(story_kwords.id) AS n, story_kwords.story_id AS story_id FROM stories_keywords_to_stories AS story_kwords LEFT JOIN stories_keywords AS kwords ON kwords.id = story_kwords.keyword_id WHERE kwords.language = 'en' AND (kwords.name = 'caucasian') GROUP BY story_kwords.story_id) AS some_k_words_count ON some_k_words_count.story_id = stories_local.story_id WHERE stories_local.language = 'en' GROUP BY stories_local.story_id ) AS search_order ON search_order.story_id = stories.id LEFT JOIN (SELECT story_id, GROUP_CONCAT(k_word SEPARATOR ', ') AS story_k_words, COALESCE(SUM(matcher), 0) AS n FROM -- (SELECT id AS story_id, 'word' AS k_word, 0 AS matcher FROM stories) (SELECT stories_k_words.story_id AS story_id, k_words.name AS k_word, 1 AS matcher FROM stories_keywords_to_stories AS stories_k_words LEFT JOIN stories_keywords AS k_words ON k_words.id = stories_k_words.keyword_id WHERE k_words.language = 'en' AND k_words.name IN ('caucasian') ) AS t GROUP BY story_id ) AS k_words_matches ON k_words_matches.story_id = stories.id LEFT JOIN ( SELECT SUM(visits) AS visits, content_id AS story_id FROM pages_visits WHERE content_type = 'story' AND dt >= 20230903 GROUP BY content_id ) AS visits ON visits.story_id = stories.id LEFT JOIN ( SELECT stories_videos.story_id AS story_id, COUNT(videos_downloads.id) AS story_downloads FROM videos_downloads LEFT JOIN stories_videos ON stories_videos.video_id = videos_downloads.video_id WHERE videos_downloads.dt >= 20230903 GROUP BY stories_videos.story_id ) AS downloads ON downloads.story_id = stories.id WHERE stories.state = 1 AND stories.encoded = 1 AND (( stories_local.name LIKE '%caucasian%' OR stories_local.text_description LIKE '%caucasian%' OR stories.id IN ( SELECT DISTINCT keywords_stories.story_id FROM stories_keywords LEFT JOIN stories_keywords_to_stories AS keywords_stories ON keywords_stories.keyword_id = stories_keywords.id WHERE stories_keywords.name LIKE '%caucasian%' ) ) OR -- exact match by story preview file name stories.id IN ( SELECT story_id FROM videos_encoded WHERE file_name = 'caucasian' ) OR -- exact match by video file name stories.id IN ( SELECT story_id FROM stories_videos WHERE video_id IN ( SELECT video_id FROM videos_encoded WHERE file_name = 'caucasian' ) ) ) AND stories.id NOT IN (SELECT story_id FROM stories_categories_to_stories WHERE category_id = 44 ) ) GROUP BY keyword_id HAVING COUNT(id) = 1208 ) AND language = 'en' ORDER BY name ASC]
--
Query execution was interrupted (max_statement_time exceeded)