Оптимизация SQL запроса - нужна помощь

[kirush]

На 003 использовали такой запрос для выдергивания связки IP<>MAC, чтобы в дальнейшем загнать в dhcpd.conf

Код: Выделить всё

SELECT COUNT(*) AS _cnt,(4294967295 & ip_groups.ip) AS _uip,users.id AS uid,INET_NTOA(4294967295 & ip_groups.ip) AS uip,ip_groups.mac AS umac FROM users,ip_groups,service_links,iptraffic_service_links WHERE ip_groups.is_deleted=0 AND ip_groups.ip_group_id=iptraffic_service_links.ip_group_id AND iptraffic_service_links.id=service_links.id AND service_links.user_id=users.id GROUP BY _uip,uid,uip,umac ORDER BY _uip ASC;

раньше он выполнялся за несколько секунд.
После обновления на 005, запрос выполняется полторы минуты ;(
Можно его как то оптимизировать?
DB: Mariadb102

[Magnum72]

На 003 использовали такой запрос для выдергивания связки IP<>MAC, чтобы в дальнейшем загнать в dhcpd.conf

Код: Выделить всё

SELECT COUNT(*) AS _cnt,(4294967295 & ip_groups.ip) AS _uip,users.id AS uid,INET_NTOA(4294967295 & ip_groups.ip) AS uip,ip_groups.mac AS umac FROM users,ip_groups,service_links,iptraffic_service_links WHERE ip_groups.is_deleted=0 AND ip_groups.ip_group_id=iptraffic_service_links.ip_group_id AND iptraffic_service_links.id=service_links.id AND service_links.user_id=users.id GROUP BY _uip,uid,uip,umac ORDER BY _uip ASC;

раньше он выполнялся за несколько секунд.
После обновления на 005, запрос выполняется полторы минуты ;(
Можно его как то оптимизировать?
DB: Mariadb102

Думаю можно, покажи какие поля из результата ты реально используешь.

[kirush]

Хотелось бы все оставить, как и в этом запросе, чтобы не переписывать скрипт.
А может проблема появилась не из за перехода с 003 на 005, а из за того что перешел с mysql 5.7 на mariadb 10.2 и стоит попробовать вернуться назад?

[Magnum72]

Хотелось бы все оставить, как и в этом запросе, чтобы не переписывать скрипт.
А может проблема появилась не из за перехода с 003 на 005, а из за того что перешел с mysql 5.7 на mariadb 10.2 и стоит попробовать вернуться назад?

Код: Выделить всё

SELECT
  (4294967295 & ipg.ip) AS _uip,
  us.id AS uid,
  INET_NTOA(4294967295 & ipg.ip) AS uip,
  ipg.mac AS umac
FROM
  users AS us,
  ip_groups AS ipg,
  service_links AS sl,
  iptraffic_service_links AS isl
WHERE 1
  AND ipg.is_deleted = 0
  AND sl.is_deleted = 0
  AND ipg.ip_group_id = isl.ip_group_id
  AND isl.id = sl.id
  AND sl.user_id = us.id
ORDER BY _uip ASC
;

попробуй так, в новой версии в ип группах удаленные строки больше не хранятся поэтому все что is_deleted = 1 можешь смело удалить

[kirush]

до удаления:
1257 rows in set (1 min 24.74 sec)

delete from ip_groups where is_deleted='1';
Query OK, 11269 rows affected (0.27 sec)

после:
1257 rows in set (1 min 10.38 sec)

[Magnum72]

до удаления:
1257 rows in set (1 min 24.74 sec)

delete from ip_groups where is_deleted='1';
Query OK, 11269 rows affected (0.27 sec)

после:
1257 rows in set (1 min 10.38 sec)

Эксплайн запроса покажи

[kirush]

MariaDB [UTM5_last3]> explain SELECT (4294967295 & ipg.ip) AS _uip, us.id AS uid, INET_NTOA(4294967295 & ipg.ip) AS uip, ipg.mac AS umac FROM users AS us, ip_groups AS ipg, service_links AS sl, iptraffic_serice_links AS isl WHERE 1 AND ipg.is_deleted = 0 AND sl.is_deleted = 0 AND ipg.ip_group_id = isl.ip_group_id AND isl.id = sl.id AND sl.user_id = us.id ORDER BY _uip ASC;
+------+-------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------+---------+----------------------------+-------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------+---------+----------------------------+-------+-------------------------------------------------+
| 1 | SIMPLE | us | index | PRIMARY | PRIMARY | 4 | NULL | 1 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | isl | index | PRIMARY | index_045eb943ad178e7ac6ea41bdc888b2b | 13 | NULL | 12929 | Using index; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | ipg | ref | index_bbf43ea959eabadc856f5d9cb702d771,index_264d0ebccce540730e5da3d221dfa3d9,index_e46a972fea6eaeefa7594e0da66d5e7c,index_0192e8f0f9c5d59e90338613910cd1b9 | index_264d0ebccce540730e5da3d221df3d9 | 4 | UTM5_last3.isl.ip_group_id | 1 | Using index condition |
| 1 | SIMPLE | sl | eq_ref | PRIMARY | PRIMARY | 4 | UTM5_last3.isl.id | 1 | Using where |
+------+-------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------+---------+----------------------------+-------+-------------------------------------------------+
4 rows in set (0.00 sec)

[Magnum72]

Странно, у меня все ровно тоже самое, только абонентов больше в несколько десятков раз, и запрос отрабатывает за 2-3 сек. Думаю надо тюнить настройки.

[kirush]

попробую на mysql вместо mariadb для начала.

[kirush]

На mysql 5.7 Ваш запрос занял:
1257 rows in set (1.88 sec)
мой старый:
1260 rows in set (0.08 sec)
куда то 3 записи потерялись.

Придется видимо возвращаться к mysql.