Всем привет, столкнулся на своей практике с проектом, где нужно разобраться с долгой загрузкой страниц каталога предприятий (какой проект написать не могу). Потратив несколько часов на поиск причины, выяснилось что при запросе каталога предприятий, бд грузит проц сервера, дальше поковыряв логи, все свелось к одной процедуре. На одну страницу выводится всего шесть элементов, одна и та же процедура используется для категорий и подкатегорий, а так же и поиск в ней. Разрабатывал проект не я, я третий или 20 веб-мастер у этого проекта, информации как всегда нету у владельца проекта. По этому прошу помощи знающих людей, помогите пожалуйста найти проблемное место в процедуре.

авторDELIMITER $$

CREATE PROCEDURE `company_get_list_for_index_test`(IN param_lang VARCHAR(20), IN param_uri VARCHAR(1000), IN param_search_text VARCHAR(200), IN param_from INT, IN param_count INT)
sp:
BEGIN

DECLARE var_lang_id INT DEFAULT 0;
DECLARE var_category_id INT DEFAULT 0;
DECLARE var_main_category_id INT DEFAULT 0;

SET var_lang_id = IFNULL((SELECT id FROM lang WHERE alias = param_lang), 0);
SET var_category_id = (SELECT id FROM categories WHERE uri = param_uri);

SET var_main_category_id = (sf_get_main_category_id_by_category_id(var_category_id));

UPDATE companies AS c SET
rating = (SELECT ccs.sort_no FROM category_companies_sort AS ccs WHERE ccs.company_id = c.id AND category_id = var_main_category_id);
UPDATE companies AS c SET
rating = 0
WHERE rating IS NULL;

UPDATE companies AS c SET
rating = rating + IF(c.status_payment = 1, c.status_payment * 1000, 0);


PREPARE stmt FROM
"SELECT SQL_CALC_FOUND_ROWS
c.id,
c.map,
c.logo,
c.banner,
c.site,
c.status_id,
c.created_at,
c.created_by,
c.modified_at,
c.modified_by,
c.status_payment,
cl.lang_id,
cl.`name`,
cl.brief_text,
cl.full_text,
cl.address,
cl.phone_1,
cl.phone_2,
cl.phone_3,
cl.phone_4,
cl.phone_5,
cl.contacts_info,
(SELECT id FROM category_companies_sort WHERE company_id = c.id AND category_id = ? LIMIT 1) AS sort_id,
IF((SELECT sort_no FROM category_companies_sort WHERE company_id = c.id AND category_id = ?) IS NULL, 0,
(SELECT sort_no FROM category_companies_sort WHERE company_id = c.id AND category_id = ?)) AS sort_no,
IF(c.status_payment = 1, c.status_payment * 1000, 0) AS `status_payment_temp`
FROM companies AS c
INNER JOIN companies_lang AS cl ON c.id = cl.company_id AND cl.lang_id = ?
WHERE
(EXISTS(
SELECT * FROM companies_categories AS cc
WHERE cc.company_id = c.id
AND cc.category_id IN (SELECT id FROM categories WHERE uri LIKE CONCAT(?, '%'))
)OR ? = ''
)AND(
(MATCH (c.site, cl.name, cl.brief_text, cl.full_text, cl.address, cl.phone_1, cl.phone_2, cl.phone_3, cl.phone_4, cl.phone_5) AGAINST
(CONCAT('\"', ?, '\"') IN BOOLEAN MODE))
OR
? = ''
)
ORDER BY
rating ASC
LIMIT ?,?;";

SET @stmt_from = param_from;
SET @stmt_count = param_count;
SET @stmt_lang_id = var_lang_id;
SET @stmt_uri = param_uri;
SET @stmt_search_text = param_search_text;
SET @stmt_main_category_id = var_main_category_id;
SET @stmt_m_category_id = var_category_id;

EXECUTE stmt
USING
@stmt_m_category_id,
@stmt_main_category_id,
@stmt_main_category_id,
@stmt_lang_id,
@stmt_m_category_id
@stmt_uri,
@stmt_search_text,
@stmt_search_text,
@stmt_from,
@stmt_count;

SELECT FOUND_ROWS() AS 'count';
END$$

DELIMITER ;


Я предполагаю что эта часть кода тормозит:

авторEXISTS(
SELECT * FROM companies_categories AS cc
WHERE cc.company_id = c.id
AND cc.category_id IN (SELECT id FROM categories WHERE uri LIKE CONCAT(?, '%'))
)OR ? = ''

MasterZiv,

Я включил, и только этот запрос в логах фигурирует, особенно когда есть переход на страницу подкатегории предприятий

awotarosMasterZiv,

Я включил, и только этот запрос в логах фигурирует, особенно когда есть переход на страницу подкатегории предприятий

Дело в том что весь сайт при этом виснет ))

miksoft,

Версия 5.5.32

вадя,

Вы думаете что в этом проблема ?

Я убрал PREPARE и получилось вот:

авторDELIMITER $$

CREATE PROCEDURE `sp_company_get_list_for_index_nw`(IN param_lang VARCHAR(20), IN param_uri VARCHAR(1000), IN param_search_text VARCHAR(200), IN param_from INT, IN param_count INT)
sp:
BEGIN

DECLARE var_lang_id INT DEFAULT 0;
DECLARE var_category_id INT DEFAULT 0;
DECLARE var_main_category_id INT DEFAULT 0;

SET var_lang_id = IFNULL((SELECT id FROM lang WHERE alias = param_lang), 0);

SET var_category_id = (SELECT id FROM categories WHERE uri = param_uri);

SET var_main_category_id = (sf_get_main_category_id_by_category_id(var_category_id));

UPDATE companies AS c SET
rating = (SELECT ccs.sort_no FROM category_companies_sort AS ccs WHERE ccs.company_id = c.id AND category_id = var_main_category_id);
UPDATE companies AS c SET
rating = 0
WHERE rating IS NULL;

UPDATE companies AS c SET
rating = rating + IF(c.status_payment = 1, c.status_payment * 1000, 0);


SELECT SQL_CALC_FOUND_ROWS
c.id,
c.map,
c.logo,
c.banner,
c.site,
c.status_id,
c.created_at,
c.created_by,
c.modified_at,
c.modified_by,
c.status_payment,
cl.lang_id,
cl.`name`,
cl.brief_text,
cl.full_text,
cl.address,
cl.phone_1,
cl.phone_2,
cl.phone_3,
cl.phone_4,
cl.phone_5,
cl.contacts_info,
(SELECT id FROM category_companies_sort WHERE company_id = c.id AND category_id = var_category_id LIMIT 1) AS sort_id,
IF((SELECT sort_no FROM category_companies_sort WHERE company_id = c.id AND category_id = var_main_category_id) IS NULL, 0,
(SELECT sort_no FROM category_companies_sort WHERE company_id = c.id AND category_id = var_main_category_id)) AS sort_no,
IF(c.status_payment = 1, c.status_payment * 1000, 0) AS `status_payment_temp`
FROM companies AS c
INNER JOIN companies_lang AS cl ON c.id = cl.company_id AND cl.lang_id = var_lang_id
WHERE
(EXISTS(
SELECT * FROM companies_categories AS cc
WHERE cc.company_id = c.id
AND cc.category_id IN (SELECT id FROM categories WHERE uri LIKE CONCAT(?, '%'))
)OR param_uri = ''
)AND(
(MATCH (c.site, cl.name, cl.brief_text, cl.full_text, cl.address, cl.phone_1, cl.phone_2, cl.phone_3, cl.phone_4, cl.phone_5) AGAINST
(CONCAT('\"', param_search_text, '\"') IN BOOLEAN MODE))
OR
param_search_text = ''
)
ORDER BY
rating ASC
LIMIT param_from,param_count;


SELECT FOUND_ROWS() AS 'count';
END$$

DELIMITER ;


Я тоже согласен что в IN проблема. Как мне обойтись без IN, убрать оттуда, ведь при помощи IN он выбираете материалы по совпадению.

авторEXISTS(
SELECT * FROM companies_categories AS cc
WHERE cc.company_id = c.id
AND cc.category_id IN (SELECT id FROM categories WHERE uri LIKE CONCAT(param_uri, '%'))
)OR param_uri = ''


А что касается SQL_CALC_FOUND_ROWS да я согласен что он лишний и что он считает все записи не зависимо от лимита (хотя он используется для постраничной навигации), с этим вопросом я попробую разобраться, но меня больше всего волнует IN, несколько запросов таких где участвует в этой процедуре IN по сути грузят mysql и в процессах долго висят (это если несколько посетителе открывают подкатегорию предприятий, в этом случае срабатывает IN)

awotaros,

Вместо вопросика там param_uri

автор(EXISTS(
SELECT * FROM companies_categories AS cc
WHERE cc.company_id = c.id
AND cc.category_id IN (SELECT id FROM categories WHERE uri LIKE CONCAT(param_uri, '%'))
)OR param_uri = ''

Вопрос решил, вот несколько работающих вариантов:

Это решение от Akina, вот его ответ 21201983 за что ему огромное спасибо




И вот еще один вариант с JOIN, ребята тут подсказали:




Второй вариант тоже работает, но я пока использую вариант от Akina, работает хорошо, а вариант с JOIN только тестировал но не пробовал на проде, мне кажется что первый вариант быстрее работает. Думаю многим пригодится кто столкнется с процедурами и где есть зависания mysql.

Вот готовый вариант процедуры:




Спасибо большое за помощь, всем !