Поиск в двух таблицах

Добрый день. Можете помочь оптимизировать как то запрос

Код (Text):

1. SELECT apache_logs.*
2. FROM apache_logs
3. WHERE (apache_logs.user_agent In (SELECT bad_user_agents.name FROM bad_user_agents))
4. GROUP BY ip

Логика такая. Есть две таблицы.
Первая два поля ид и user_agent
Вторая таблица ид, user_agent и другие данные
Надо проверить каждую строку из второй таблицы(столбец user_agent) на совпадение любому из значений списка столбца "user_agent" в таблице один

Таблицы никак не связаны между собой. В данный момент запрос стоит как в начале написал, но он долго работает, есть какие нибудь идеи как оптимизировать можно, увеличив скорость?

Поидее там быстро должно все работать, в первой таблице список примерно из 150 user_agent всего. И каждую строку с столбцом "user_agent" из таблицы два, а их всего 25000, надо проверить на совпадение любому из этого списка таблицы один. Запрос выполняется секунд 80. Долго слишком. Можете помочь?

Связать по имени таблицы я не могу, так как список user_agent в таблице 2 намного больше чем в таблице один, и он постоянно увеличивается там, имею ввиду количество уникальных.

 

Такой подход я тоже делал, прироста скорости не получил увы..


Как я могу сделать соответствующие поля индексами? Я понимаю что так будет быстрее, но у меня нет таблицы "справочника" В таблице 1 один список, в таблице 2 другой список и он непостоянен, он увеличивается постоянно, нет же фиксированного списка user_agent, где бы просто индексы ставил что в первой, что во второй таблице, или я чего то не так понимаю?

 

Хотя запрос я делал так

Код (Text):

1. SELECT apache_logs.*
2. FROM bad_user_agents
3. LEFT JOIN apache_logs ON (bad_user_agents.name = apache_logs.user_agent)
4. WHERE apache_logs.user_agent IS NOT null
5. GROUP by ip

И по времени он идентичен запросу

Код (Text):

1. SELECT apache_logs.*
2. FROM apache_logs
3. WHERE (apache_logs.user_agent In (SELECT bad_user_agents.name FROM bad_user_agents))
4. GROUP BY ip

Единственное быстрее работает если уберу из первого запроса условие WHERE apache_logs.user_agent IS NOT null, то есть получается такой запрос

Код (Text):

1. SELECT apache_logs.*
2. FROM bad_user_agents
3. LEFT JOIN apache_logs ON (bad_user_agents.name = apache_logs.user_agent)
4. GROUP by ip

Он работает в 2-2,5 раза быстрее примерно, это уже хорошо, но первая строка null идет, не пойму почему, есть предположения почему это может быть?

 

bad_user_agents это не справочник, здесь лишь часть записей лежит, тут например 150 записей так таблица 1, а в таблице 2 уник записей может вообще быть 1000, и как быть тогда, где есть из таблицы один в таблице два я поставлю индекс хорошо, а в другие поля как быть? поэтому и проблема с индексом.

Про WHERE logs.user_agent IS NOT null писал что из-за этой проверки тормозится время сильно.

А вот по null строчке, как бы вычислить где это зарыто, ибо пересмотрел уже по всем параметрам, нигде нет с null не одного поля

 

prnt.sc/ie27zo

Нарисовал мое понимание индексов, разве я не верно понимаю работу?

Давно бы добавил но как я это сделаю? на картинке второй вариант, когда у меня нет всех возможных вариантов

 

А вот скрин с null https://prnt.sc/ie2i5j
Странно как то появлятся она на разных данных всегда вот так на первом месте

 

https://prnt.sc/ie3j46 так??, верно теперь?

И про нулл, мне нужно найти ip где есть хотя бы раз в запросе с данного ip плохой user agent, поэтому и группирую по ip

 

Есть плюсы, есть минусы, конкретно этот запрос, я оставил с самого первого поста который, стал работать моментально. Вопросов нет. Но другой запрос, массовый insert стал работать в два раза медленнее.

 

https://prnt.sc/ie8v7n вот индексы добавил.
Как бы есть и плюсы и минусы как раньше говорил, походу выбирать придется что то одно, куда жертвовать время.
Есть добавление и удаление данных и есть сбор статистики. Дак вот, обработать файл 100мб 350000строк примерно на расчеты(статистику) тратилось 90 секунд у меня, прочитав статью про индексы что вы дали, стало 6-7 секунд - это плюс.

Но те же 100мб 350000строк например загрузить в таблицу или удалить из нее стало раза в 4 медленнее. Раньше распарсить и загрузить уходило секунд 20 (время везде меряю функцией microtime(1)), сейчас 80. Индексы которые добавил приложил на скрине.

Есть ли возможность сохранить время на расчет результатов, которое сейчас удалось добиться, но при этом и время вернуть которое раньше было на добавление записей в бд таблицу?
--- Добавлено ---
а добавляю записи как в дампе mysql идет, массовый insert, количество подключений к бд минимально, просто запрос длинный идет и все

 

1. Прочту завтра статью, спасибо.
2. Я при тестах обнаружил что вся оптимизация идет в трубу, если количество mysqli_query не минимизировано, то есть если добавлять по одной записи или по несколько, вообще по идее можно запрос делать длины такой select @@max_allowed_packet и его субд должна обработать. Вообщем в принципе делаю так же как и вы написали, может только количество записей за раз больше добавляется.
3. Это надо опробовать опять же на тестах и проверить, завтра сделаю это.

А по поводу индексов и запросов - запрос, указанный в первом посте, это лишь один из запросов, проводимых для статистики, и то он лишь составной другого запроса. А индексы все связаны с другими запросами, и там реально прирост скорости налицо стал.

Добавляю описал в пункте 2 как , а удаляю обычным не мудреным запросом delete from табличка where 1 и все, ничего хитрого.

 

По скорости, что массовый инсерт

Код (Text):

1. INSERT INTO apache_logs (ip,log_time,method_request,page,version,code,size_bytes,url_referer,user_agent) VALUES (''),(''),('');

что запрос загрузкой файла

Код (Text):

1. LOAD DATA INFILE "./test.txt"
2. INTO TABLE apache_logs
3. FIELDS TERMINATED BY '||'
4. (ip,log_time,method_request,page,version,code,size_bytes,url_referer,user_agent);

Дает одинаковый результат по скорости, что так, что так уходит на 350 000 распарсить каждую на 10 составляющих и добавить в бд уходит 20+- секунд. Важный момент БЕЗ ИНДЕКСОВ.

С индексами, указанном на скрине https://prnt.sc/ie8v7n на туже самую работу что в первом, что во втором запросе уходит примерно одинаково +-80секунд. Статью вашу прочел, разницу никак не могу получить

 

В данный момент вижу только такой вариант, использовать конструкцию

Код (Text):

1. ALTER TABLE apache_logs DROP INDEX user_agent, DROP INDEX ip_log_time, DROP INDEX ip_code;
2. DELETE FROM apache_logs WHERE 1;
3. ALTER TABLE apache_logs ADD INDEX user_agent(user_agent),ADD INDEX ip_log_time(ip,log_time),ADD INDEX ip_code(ip,code);

Для удаления и добавления данных, как вы писали в 3 пункте своего сообщения, всеравно побыстрее это дело работает. С таким подходом 30 секунд уходит, я все тесты провожу на на одном файле 350 000который. Но блин, 30 секунд это на грани, надо уменьшить как то.

 

Вопрос такой, какие бы правильно все таки было создать индексы, вся работа идет по одной таблице, кроме одного подзароса

Код (Text):

1. SELECT Y.ip, COUNT(Y.count_all), Z.count_black, Z.count_black_time, Y.count_code, Y.user_agent
2.     FROM
3.         (SELECT apache_logs.ip AS ip, COUNT(apache_logs.ip) AS count_all, SUM(CASE WHEN apache_logs.code = '404' THEN 1 ELSE 0 END) AS count_code, X.count_UA AS user_agent
4.             FROM
5.                  (SELECT ip, COUNT(user_agent) AS count_UA
6.                     FROM apache_logs
7.                     WHERE (apache_logs.user_agent In (SELECT bad_user_agents.name FROM bad_user_agents))
8.                     GROUP BY ip) AS X
9.             RIGHT JOIN apache_logs ON (apache_logs.ip = X.ip)
10.             GROUP BY ip) AS Y
11.        
12.     LEFT JOIN
13.  
14.         (SELECT B.ip, COUNT(B.count_sequence) AS count_black, SUM(B.count_sequence) AS count_black_time
15.             FROM
16.                  (SELECT A.ip, A.count_sequence
17.                     FROM
18.                          (SELECT ip, log_time, COUNT(ip) AS count_sequence
19.                               FROM apache_logs
20.                               GROUP BY ip, log_time)  AS A
21.                     WHERE A.count_sequence > 2)  AS B
22.             GROUP BY B.ip) AS Z
23.  
24.     ON (Y.ip = Z.ip)
25.     GROUP BY Y.ip

Я создал такие индексы

Код (Text):

1. CREATE INDEX user_agent ON apache_logs(user_agent);
2. CREATE INDEX ip_log_time ON apache_logs(ip,log_time);
3. CREATE INDEX ip_code ON apache_logs(ip,code);

Может как то надо по другому? все поля таблицы неуникальные. Запутался я чета уже.

Вот explain запроса
https://prnt.sc/if0zy4