Почему order by тормозит запрос?

Question

[Вячеслав Барсуков]

Добрый день, есть запрос, как бы я его не переписывал, с join или без - время выполнения не уменьшается.

# Query_time: 4.511916 Lock_time: 0.000202 Rows_sent: 48 Rows_examined: 763752

select 
        (select
              url
        from
             image
        where
            type_id = catalog.id and
            name in('F3M') and
            type = 'catalog'
         limit 1) as url, 
         catalog.id, 
         catalog.id1c, 
         catalog.naimenovanie, 
         catalog.naimenovanieEng, 
         catalog.naimenovaniePolnoe, 
         catalog.naimenovaniePolnoeEng, 
         catalog.collection, 
         catalog.tipTovara, 
         catalog.nazvanieModeli, 
         catalog.tsvetOpravyOchkov, 
         catalog.naimenovanieVKollektsii, 
         catalog.productСode, 
         (select price.cost from price where
            price.catalog_id1c = catalog.id1c   
            AND price.price_id1c = 'ee6d5270-6799-11da-a71d-001279911df8'
            AND price.currency = 'руб'
         ) as cost
         from `catalog` where 
                 (select price.cost from price where
                     price.catalog_id1c = catalog.id1c   
                     AND price.price_id1c = 'ee6d5270-6799-11da-a71d-001279911df8'
                     AND price.currency = 'руб'
                 )>0
             and `catalog`.`active` = '1' and (`catalog`.`tipTovara` = 'Очки' or `catalog`.`tipTovara` = 'Оправа') and 
                 	(select sum(count) from residue where
                        residue.catalog_id1c = catalog.id1c
                            and
                        residue.sklad_id1c in(
                                    'c422abe6-d2ca-11db-937d-001279911df8',
                                    '1536edb8-7854-11e1-9812-984be17c40d2',
                                    'c507a785-786c-11e1-9812-984be17c40d2',
                                    '3f99598c-e3f3-11e0-9ffe-0015178161cc',
                                    '9c772c75-b4b2-11db-a17a-001279911df8',
                                    'ec11b60c-ea59-11e0-aaca-0015178161cc',
                                    '3f6fbc07-b0dc-11e0-ad81-000423c85d89',
                                    '3f6fbc09-b0dc-11e0-ad81-000423c85d89',
                                    'ffbe189d-24f3-11e5-b1c2-984be17c40d3',
                                    '94122d27-79cd-11da-b3bd-001279911df8',
                                    '94122d28-79cd-11da-b3bd-001279911df8',
                                    '94122d29-79cd-11da-b3bd-001279911df8',
                                    '94122d2a-79cd-11da-b3bd-001279911df8',
                                    '94122d2c-79cd-11da-b3bd-001279911df8',
                                    '94122d2d-79cd-11da-b3bd-001279911df8',
                                    '94122d2e-79cd-11da-b3bd-001279911df8',
                                    '94122d34-79cd-11da-b3bd-001279911df8',
                                    '296d8dc7-a38c-11e5-b64c-984be17c40d3',
                                    '296d8dc8-a38c-11e5-b64c-984be17c40d3',
                                    '296d8dc9-a38c-11e5-b64c-984be17c40d3',
                                    '9cf2ae52-f54c-11db-b74b-0015171080ec',
                                    '9cf2ae53-f54c-11db-b74b-0015171080ec',
                                    '110cb65f-9951-11dc-ba45-0015171080ec',
                                    '959e1e4a-fd24-11e4-ba61-984be17c40d3',
                                    '671eb0b6-b426-11dc-bf75-0015171080ec',
                                    '671eb0b7-b426-11dc-bf75-0015171080ec',
                                    '671eb0b8-b426-11dc-bf75-0015171080ec')
                    )>0
             and 
        SUBSTRING(catalog.exp,2)<60
         group by `catalog`.`id1c` order by case when catalog.exp<100 then 1 else 0 end/*, SUBSTRING(catalog.exp,2) asc, SUBSTRING(catalog.exp, 1, 1) asc*/, catalog.exp asc, catalog.created_at desc limit 48;

explain

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	catalog	NULL	ref	id1c,tipTovara,active	active	1	const	53458	92.41	Using index condition; Using where; Using filesort
5	DEPENDENT SUBQUERY	residue	NULL	ref	catalog_id1c,sklad_id1c	catalog_id1c	767	catalog1c.catalog.id1c	4	66.01	Using where
4	DEPENDENT SUBQUERY	price	NULL	ref	price_id1c,catalog_id1c,currency	catalog_id1c	767	catalog1c.catalog.id1c	6	6.61	Using where
3	DEPENDENT SUBQUERY	price	NULL	ref	price_id1c,catalog_id1c,currency	catalog_id1c	767	func	6	6.61	Using where
2	DEPENDENT SUBQUERY	image	NULL	ref	name,type_id,type	type	925	const,func,const	1	100.00	NULL

Методом перебора вычислил что тормозят order by

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	catalog	NULL	index	id1c,tipTovara,active	id1c	767	NULL	96	46.20	Using where
5	DEPENDENT SUBQUERY	residue	NULL	ref	catalog_id1c,sklad_id1c	catalog_id1c	767	catalog1c.catalog.id1c	4	66.05	Using where
4	DEPENDENT SUBQUERY	price	NULL	ref	price_id1c,catalog_id1c,currency	catalog_id1c	767	catalog1c.catalog.id1c	6	6.61	Using where
3	DEPENDENT SUBQUERY	price	NULL	ref	price_id1c,catalog_id1c,currency	catalog_id1c	767	func	6	6.61	Using where
2	DEPENDENT SUBQUERY	image	NULL	ref	name,type_id,type	type	925	const,func,const	1	100.00	NULL

индексы используются, можно как то переписать запрос что бы исключить order by? пытался через union объединять результаты, но время не уменьшилось.

запрос вида

select 
         catalog.id, 
         catalog.id1c, 
         catalog.naimenovanie, 
         catalog.naimenovanieEng, 
         catalog.naimenovaniePolnoe, 
         catalog.naimenovaniePolnoeEng, 
         catalog.collection, 
         catalog.tipTovara, 
         catalog.nazvanieModeli, 
         catalog.tsvetOpravyOchkov, 
         catalog.naimenovanieVKollektsii, 
         catalog.productСode
         from `catalog` where 
         `catalog`.`active` = '1' and (`catalog`.`tipTovara` = 'Очки' or `catalog`.`tipTovara` = 'Оправа')  
          group by `catalog`.`id1c` order by case when catalog.exp<100 then 1 else 0 end, catalog.exp asc, catalog.created_at desc

выполняется 2 сек.

Comments

[Oleg Shevelev]

Избавляйтесь от вложенных запросов:)

[Вячеслав Барсуков]

Oleg Shevelev: я делал через join, не помогает

[Oleg Shevelev]

Вячеслав Барсуков: разбейте логику на несколько отдельных запросов, упрощайте, а не усложняйте...

[Вячеслав Барсуков]

Oleg Shevelev: мне нужно вытащить с бд определенные строки, а не все и циклом проходить по этим строкам на стороне приложения.

[Oleg Shevelev]

(select price.cost from price where
            price.catalog_id1c = catalog.id1c   
            AND price.price_id1c = 'ee6d5270-6799-11da-a71d-001279911df8'
            AND price.currency = 'руб'
         ) as cost

Первый кандидат что бы не пихать его в общий SQL - сделаете потом отдельный запрос с указанием price.catalog_id1c IN (список идентификаторов) и обновите данные что получили ранее...

select sum(count) from residue where
                        residue.catalog_id1c = catalog.id1c
                            and
                        residue.sklad_id1c in(

Кандидат на то что бы вначале подготовить эти цифры и или время от времени их обновлять или делать временную таблицу с идентификаторами...

Как только это сделаете у вас будут "простые" линейные запросы.

И да, если у вас нет нужных индексов то ничего не поможет:)

Answer 1

[Rsa97]

Потому что в order by используется case, соответственно при сортировке не используются индексы.

Comments

[Вячеслав Барсуков]

При удалении case when catalog.exp<100 then 1 else 0 end, проблема не решается

[Rsa97]

Вячеслав Барсуков: Using filesort при этом остаётся? Если да, то попробуйте добавить составной индекс для сортировки.

Answer 2

[Constantine]

Order by, Group by работают через filesort, а это тормоза.
Проведите денормализацию бд.

Кстати, explain extended, показывает чуть больше инфы.

Comments

[Вячеслав Барсуков]

Я все подобные операции уже в оперативную память перенес, прирост значительный. При explain extended ничего не меняется в выводе.