Запрос с count(). Запрос выполнен за 1.718 секунд.:
WITH t1 AS (
SELECT idBook AS id
FROM search_book
WHERE count_words >= 2 AND
min_len >= 3 AND
max_len <= 5 AND
(n LIKE '% мир %' OR
n LIKE '%новый%')
),
t2 AS (
SELECT id
FROM books
WHERE dateYearPublished IN (2000)
),
t3 AS (
SELECT id
FROM books
WHERE pagesCount BETWEEN 200 AND 1000
),
t4 AS (
SELECT t1.id
FROM t1
JOIN
t2 ON t1.id = t2.id
JOIN
t3 ON t2.id = t3.id
)
SELECT COUNT( * )
FROM t4;
Без count(). Запрос выполнен за 0.334 секунд.:
WITH t1 AS (
SELECT idBook AS id
FROM search_book
WHERE count_words >= 2 AND
min_len >= 3 AND
max_len <= 5 AND
(n LIKE '% мир %' OR
n LIKE '%новый%')
),
t2 AS (
SELECT id
FROM books
WHERE dateYearPublished IN (2000)
),
t3 AS (
SELECT id
FROM books
WHERE pagesCount BETWEEN 200 AND 1000
),
t4 AS (
SELECT t1.id
FROM t1
JOIN
t2 ON t1.id = t2.id
JOIN
t3 ON t2.id = t3.id
)
SELECT *
FROM t4;
search_book: 1.5 млн. записей.
books: 1.5 млн. записей.