[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(' professional ', stories_local.name) > 0
-- OR
-- LOCATE(' professional ', stories_local.text_description_for_search) > 0,
-- 1 = 1,
exact_phrase_matches.story_id IS NOT NULL,
10,
IF(
-- (LOCATE(' professional ', stories_local.name) > 0)
-- OR
-- (LOCATE(' professional ', 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 = 'professional')
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 = 'professional')
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(' professional ', stories_local.name) > 0
OR
LOCATE(' professional ', 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(' professional ', stories_local.name) > 0)
OR
(LOCATE(' professional ', 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 = 'professional')
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 = 'professional')
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 ('professional')
)
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 '%professional%'
OR
stories_local.text_description LIKE '%professional%'
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 '%professional%'
)
) OR
-- exact match by story preview file name
stories.id IN (
SELECT story_id
FROM videos_encoded
WHERE file_name = 'professional'
) 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 = 'professional'
)
)
) AND stories.id NOT IN
(SELECT story_id
FROM stories_categories_to_stories
WHERE category_id = 44
)
)
GROUP BY keyword_id
HAVING COUNT(id) = 1351
)
AND language = 'en'
ORDER BY name ASC]
--
Query execution was interrupted (max_statement_time exceeded)