🇻
Size: a a a
2020 May 23
А
эт да
А
Александр
DDL вышлите
cscart_discussion
cscart_discussion_posts
cscart_discussion_rating
cscart_discussion
cscart_discussion_posts
cscart_discussion_rating
размеры таблиц в первую очередь интересуют
VS
Александр
DDL вышлите
cscart_discussion
cscart_discussion_posts
cscart_discussion_rating
cscart_discussion
cscart_discussion_posts
cscart_discussion_rating
CREATE TABLE
PRIMARY KEY (
UNIQUE KEY
KEY
) ENGINE=InnoDB AUTO_INCREMENT=506942 DEFAULT CHARSET=utf8
CREATE TABLE
PRIMARY KEY (
KEY
KEY
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8
CREATE TABLE
PRIMARY KEY (
KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8
cscart_discussion (thread_id mediumint unsigned NOT NULL AUTO_INCREMENT,object_id mediumint unsigned NOT NULL DEFAULT '0',object_type char(1) NOT NULL DEFAULT '',type char(1) NOT NULL DEFAULT 'D',company_id int unsigned NOT NULL DEFAULT '0',PRIMARY KEY (
thread_id),UNIQUE KEY
object_id (object_id,object_type,company_id),KEY
company_id (company_id)) ENGINE=InnoDB AUTO_INCREMENT=506942 DEFAULT CHARSET=utf8
CREATE TABLE
cscart_discussion_posts (post_id mediumint unsigned NOT NULL AUTO_INCREMENT,thread_id mediumint unsigned NOT NULL DEFAULT '0',name varchar(128) NOT NULL DEFAULT '',timestamp int unsigned NOT NULL DEFAULT '0',user_id mediumint unsigned NOT NULL DEFAULT '0',ip_address varbinary(40) NOT NULL DEFAULT '',status char(1) NOT NULL DEFAULT 'D',PRIMARY KEY (
post_id),KEY
thread_id (thread_id,ip_address),KEY
thread_id_2 (thread_id,status)) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8
CREATE TABLE
cscart_discussion_rating (rating_value tinyint unsigned NOT NULL DEFAULT '0',post_id mediumint unsigned NOT NULL DEFAULT '0',thread_id mediumint unsigned NOT NULL DEFAULT '0',PRIMARY KEY (
post_id),KEY
thread_id (thread_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8
VS
В каждой по 13 записей сейчас, кроме
cscart_discussion, там 183 тыс записейА
SELECT products.product_id
, IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product) as product
, products.product_type
, products.parent_product_id
, cscart_discussion.type AS discussion_type
, cscart_discussion.thread_id AS discussion_thread_id
, AVG(cscart_discussion_rating.rating_value) AS average_rating
FROM cscart_products as products
LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'ru'
INNER JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1
INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id
INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id
AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids))
AND cscart_categories.status IN ('A', 'H')
LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id
AND shared_descr.company_id = 1
AND shared_descr.lang_code = 'ru'
INNER JOIN cscart_discussion ON cscart_discussion.object_id = products.product_id
AND cscart_discussion.object_type = 'P'
INNER JOIN cscart_discussion_posts ON cscart_discussion_posts.thread_id = cscart_discussion.thread_id
AND cscart_discussion_posts.status = 'A'
INNER JOIN cscart_discussion_rating ON cscart_discussion.thread_id = cscart_discussion_rating.thread_id
AND cscart_discussion_rating.post_id = cscart_discussion_posts.post_id
AND cscart_discussion_rating.rating_value != 0
WHERE 1
AND cscart_categories.company_id = 1
AND (products.usergroup_ids = ''
OR FIND_IN_SET(0, products.usergroup_ids)
OR FIND_IN_SET(1, products.usergroup_ids)
)
AND products.status IN ('A')
AND prices.usergroup_id IN (0, 0, 1)
AND products.parent_product_id = 0
GROUP BY products.product_id
, IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product)
, products.product_type
, products.parent_product_id
, cscart_discussion.type
, cscart_discussion.thread_id
HAVING average_rating > 0
ORDER BY average_rating desc, products.product_id ASC
LIMIT 0, 10;
А
👆
этот запрос должен мгновенно отработать
чёт телега приглючила
этот запрос должен мгновенно отработать
чёт телега приглючила
VS
АФИГЕТЬ! 0,047sec
VS
но результат пустой =)
А
странно что пустой.... ща разберёмся
А
а какой был результат, скиньте
VS
Да, этот именно и есть пустой =) (там просто 8 вариантов этого запроса, я спутал)
А
результат исходного запроса покажите...
VS
А
вот Ваш исходный отформатированный, его прямо выполните, чтобы удостоверится что я взял правильный исходный
SELECT products.product_id
, IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product) as product
, products.product_type
, products.parent_product_id
, cscart_discussion.type AS discussion_type
, cscart_discussion.thread_id AS discussion_thread_id
, AVG(cscart_discussion_rating.rating_value) AS average_rating
FROM cscart_products as products
LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'ru'
LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1
INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id
INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id
AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids))
AND cscart_categories.status IN ('A', 'H')
LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id
AND shared_descr.company_id = 1
AND shared_descr.lang_code = 'ru'
LEFT JOIN cscart_discussion ON cscart_discussion.object_id = products.product_id
AND cscart_discussion.object_type = 'P'
LEFT JOIN cscart_discussion_posts ON cscart_discussion_posts.thread_id = cscart_discussion.thread_id
AND cscart_discussion_posts.status = 'A'
LEFT JOIN cscart_discussion_rating ON cscart_discussion.thread_id = cscart_discussion_rating.thread_id
AND cscart_discussion_rating.post_id = cscart_discussion_posts.post_id
AND cscart_discussion_rating.rating_value != 0
WHERE 1
AND cscart_categories.company_id = 1
AND (products.usergroup_ids = ''
OR FIND_IN_SET(0, products.usergroup_ids)
OR FIND_IN_SET(1, products.usergroup_ids)
)
AND products.status IN ('A')
AND prices.usergroup_id IN (0, 0, 1)
AND products.parent_product_id = 0
GROUP BY products.product_id
HAVING average_rating > 0
ORDER BY average_rating desc, products.product_id ASC
LIMIT 0, 10;
VS
VS
Это исходный
А
т.е. исходный запрос тоже пустоту возвращает? но в 200 раз дольше
VS
Именно так!