Как оптимизировать запрос?

Question

[aopil]

SELECT B.Id AS IdProxy, B.Ip, B.Country FROM sc_proxy B WHERE (SELECT COUNT(*) FROM sc_proxy_request_log C WHERE C.IdSite = '11' AND B.Ip = C.IpProxy) <= '3' ORDER BY B.Id DESC LIMIT 1

Есть таблица sc_proxy где хранятся все прокси.
Есть таблица sc_proxy_request_log в которой хранится лог запросов.

Задача получить Прокси из базы, при этом соблюдать условия:
IdSite - Это уникальный ID который указывает на лимит к конкретному сайту
3 - Это максимальное количество запросов которое нам подходит, т.е. если в sc_proxy_request_log есть 4 записи с этим прокси, то он не подходит под наше условие, т.к. не более чем 3 нам требуется.

Мой запрос фактически работает, но когда в sc_proxy_request_log становится слишком много записей, то выборка становится очень медленной.

Comments

[Дмитрий]

explain?

[Akina]

Создать индексы sc_proxy_request_log (IdSite, IpProxy) и обратный. Посмотреть по плану, какой используется. Убедиться, что форсинг другого не ускоряет. Оставить оптимальный.
Переписать на JOIN (меньше вероятность плохого плана).

Answer 1

[nozzy]

С join попробуй, как то так:

SELECT B.Id AS IdProxy, B.Ip, B.Country FROM sc_proxy B
inner join (
  SELECT IdSite, IpProxy, COUNT(*) as total FROM sc_proxy_request_log
  group by IdSite, IpProxy
) C on C.IpProxy = B.Ip
where C.IdSite = '11' and C.total <= 3

Comments

[Akina]

Совершенно бессмысленная конструкция. Отборы следует убрать в подзапрос.
Впрочем, есть некоторый шанс, что сервер проявит интеллект - но это зависит от статистики.

[aopil]

nozzy примерное время выполнение данного запроса ~20 сек

Answer 2

[Slava Rozhnev]

На первый взгляд так:

SELECT B.Id AS IdProxy, B.Ip, B.Country 
FROM sc_proxy B 
LEFT JOIN sc_proxy_request_log C ON B.Ip = C.IpProxy
WHERE C.IdSite = 11
HAVING COUNT(*) <= 3
ORDER BY B.Id DESC 
LIMIT 1

Ну и конечно же правильная индексация

Comments

[aopil]

Slava Rozhnev когда в таблице sc_proxy_request_log слишком много записей, данный запрос выполняется по 30 секунд.. точно такая же проблема в моём запросе который я указал)

[Slava Rozhnev]

aopil, сколько записей в таблице?
Возможно вам нужно партицирование

[aopil]

Slava Rozhnev, в sc_proxy_request_log 6595 всего

[Slava Rozhnev]

aopil, тогда покажите структуру таблиц и индексов

[aopil]

Slava Rozhnev,
sc_proxy:


sc_proxy_request_log:

[Slava Rozhnev]

aopil, индексы?
Сделайте пожалуйста, show create table table_name
для каждой таблицы, а результат на https://SQLize.online и ссылку сюда

[aopil]

Slava Rozhnev, https://sqlize.online/sql/mysql57/04a10ea6f63c7662...

[Slava Rozhnev]

aopil, Добавьте индекс:

CREATE INDEX `sc_proxy_request_log_ix` ON `sc_proxy_request_log` (`IdSite`);

run sql online

[aopil]

Slava Rozhnev, а зачем нужен индекс на IdSite? Почему именно на эту колонку

[Slava Rozhnev]

aopil, это поле используется для соединения таблиц