замена count >0 на EXISTS

[vaanes]

Предлагаю рассмотреть в запросах замену конструкции count(*)>0 на EXISTS, ощутимо повышает производительность и время отклика на сложных фильтрах.
Будет полезно всем у кого нет возможности прокачивать движок для ускорения.

[support]

Можно пример улучшения производительности?

[vaanes]

Например два вида одного запроса, на базе размером около 500Мб

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

 select 
  e.* 
from 
  app_entity_26 e 
where 
  e.id > 0 
  and (
    (
      FROM_UNIXTIME(field_406, '%Y-%m-%d')= date_format(
        DATE_ADD(now(), INTERVAL 0 DAY), 
        '%Y-%m-%d'
      )
    ) 
    and e.field_404 not in (179, 17, 26, 30) 
    and EXISTS (
      select 
       1
      from 
        app_entity_26_values as cv 
      where 
        cv.items_id = e.id 
        and cv.fields_id = '889' 
        and cv.value in (47)
    ) 
    and e.field_403 not in (180, 182)
  ) 
  and (
    (
      select 
        count(*) as total 
      from 
        app_entity_26_values cv 
      where 
        cv.items_id = e.id 
        and cv.fields_id = '792' 
        and cv.value = '47'
    )> 0 
    or EXISTS (
      select 
        1
      from 
        app_entity_26_values cv 
      where 
        cv.items_id = e.id 
        and cv.fields_id = '889' 
        and cv.value = '47'
    ) 
    or EXISTS (
      select 
        1
      from 
        app_entity_26_values cv 
      where 
        cv.items_id = e.id 
        and cv.fields_id = '901' 
        and cv.value = '47'
    )
    or  EXISTS (
      select 
        1
      from 
        app_entity_26_values cv 
      where 
        cv.items_id = e.id 
        and cv.fields_id = '963' 
        and cv.value = '47'
    )
    or e.created_by = '47'
  )
LIMIT 0, 200

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

 select 
  e.* 
from 
  app_entity_26 e 
where 
  e.id > 0 
  and (
    (
      FROM_UNIXTIME(field_406, '%Y-%m-%d')= date_format(
        DATE_ADD(now(), INTERVAL 0 DAY), 
        '%Y-%m-%d'
      )
    ) 
    and e.field_404 not in (179, 17, 26, 30) 
    and (
      select 
        count(*) 
      from 
        app_entity_26_values as cv 
      where 
        cv.items_id = e.id 
        and cv.fields_id = '889' 
        and cv.value in (47)
    ) > 0 
    and e.field_403 not in (180, 182)
  ) 
  and (
    (
      select 
        count(*) as total 
      from 
        app_entity_26_values cv 
      where 
        cv.items_id = e.id 
        and cv.fields_id = '792' 
        and cv.value = '47'
    )> 0 
    or (
      select 
        count(*) as total 
      from 
        app_entity_26_values cv 
      where 
        cv.items_id = e.id 
        and cv.fields_id = '889' 
        and cv.value = '47'
    )> 0 
    or (
      select 
        count(*) as total 
      from 
        app_entity_26_values cv 
      where 
        cv.items_id = e.id 
        and cv.fields_id = '901' 
        and cv.value = '47'
    )> 0 
    or (
      select 
        count(*) as total 
      from 
        app_entity_26_values cv 
      where 
        cv.items_id = e.id 
        and cv.fields_id = '963' 
        and cv.value = '47'
    )> 0 
    or e.created_by = '47'
  )
LIMIT 0, 200

explain select нам например говорит что он работает по "rows" : 152982

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

	{
		"id" : 1,
		"select_type" : "PRIMARY",
		"table" : "e",
		"type" : "range",
		"possible_keys" : "PRIMARY,idx_created_by,idx_field_403,idx_field_404",
		"key" : "PRIMARY",
		"key_len" : "4",
		"ref" : null,
		"rows" : 152982,
		"Extra" : "Using where"
	},

Берем инструмент для нагрузочного тестирования, я обычно использую JMeter, на данном примере он показывается что разница между двумя этими запросами более 150мс.
Конечно большинство особо не замечают разницу между 800мс и 600мс или добавляют ресурсы, но чем объем больше тем разница ощутимей и добавление ресурсов каждым разом все более затратней а эффекта все меньше

[support]

А если заменить запросы на: (добавил limit 1)

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

EXISTS (
      select 
        cv.id
      from 
        app_entity_26_values cv 
      where 
        cv.items_id = e.id 
        and cv.fields_id = '889' 
        and cv.value = '47'
        limit 1
    ) 

Измените все запросы в вашем примере и скажите что покажет тест в таком случае?