HAVING = BAD, WHERE = GOOD!

A cláusula WHERE é usada para filtrar linhas, enquando a cláusula HAVING é usada para filtrar grupos de linhas. O filtro de grupos é feito depois do agrupamento – e um agrupamento pode ser bem custoso para o banco. Por isso, sempre que possível, devemos primeiro filtrar as linhas usando um WHERE. Assim evitamos o tempo gasto para agrupar as linhas filtradas. Exemplos abaixo

 

Ruim (usando HAVING)

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBzZWxlY3QgZGVwYXJ0bWVudF9pZCwgYXZnKHNhbGFyeSkKICAyICBmcm9tIGVtcGxveWVlcwogIDMgIGdyb3VwIGJ5IGRlcGFydG1lbnRfaWQKICA0ICBoYXZpbmcgZGVwYXJ0bWVudF9pZCBpbiAoNDAsIDYwLCAxMTApOwoKREVQQVJUTUVOVF9JRCBBVkcoU0FMQVJZKQotLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tCiAgICAgICAgICAxMTAgICAgICAgMTAxNTQKICAgICAgICAgICA0MCAgICAgICAgNjUwMAogICAgICAgICAgIDYwICAgICAgICA1NzYw” hover_enabled=”0″]U1FMPiBzZWxlY3QgZGVwYXJ0bWVudF9pZCwgYXZnKHNhbGFyeSkKICAyICBmcm9tIGVtcGxveWVlcwogIDMgIGdyb3VwIGJ5IGRlcGFydG1lbnRfaWQKICA0ICBoYXZpbmcgZGVwYXJ0bWVudF9pZCBpbiAoNDAsIDYwLCAxMTApOwoKREVQQVJUTUVOVF9JRCBBVkcoU0FMQVJZKQotLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tCiAgICAgICAgICAxMTAgICAgICAgMTAxNTQKICAgICAgICAgICA0MCAgICAgICAgNjUwMAogICAgICAgICAgIDYwICAgICAgICA1NzYw[/et_pb_dmb_code_snippet]

Bom (usando WHERE)

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBzZWxlY3QgZGVwYXJ0bWVudF9pZCwgYXZnKHNhbGFyeSkKIDIgIGZyb20gZW1wbG95ZWVzCiAzICBXSEVSRSBkZXBhcnRtZW50X2lkIGluICg0MCwgNjAsIDExMCkKIDQgIEdST1VQIEJZIGRlcGFydG1lbnRfaWQ7CgpERVBBUlRNRU5UX0lEIEFWRyhTQUxBUlkpCiAtLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tCiA0MCAgICAgICAgNjUwMAogNjAgICAgICAgIDU3NjAKIDExMCAgICAgICAxMDE1NA==” hover_enabled=”0″]U1FMPiBzZWxlY3QgZGVwYXJ0bWVudF9pZCwgYXZnKHNhbGFyeSkKIDIgIGZyb20gZW1wbG95ZWVzCiAzICBXSEVSRSBkZXBhcnRtZW50X2lkIGluICg0MCwgNjAsIDExMCkKIDQgIEdST1VQIEJZIGRlcGFydG1lbnRfaWQ7CgpERVBBUlRNRU5UX0lEIEFWRyhTQUxBUlkpCiAtLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tCiA0MCAgICAgICAgNjUwMAogNjAgICAgICAgIDU3NjAKIDExMCAgICAgICAxMDE1NA==[/et_pb_dmb_code_snippet]

UNION = BAD, UNION ALL = GOOD!

Ao recuperar registros semelhantes de duas tabelas, UNION é usado para remover as linhas duplicadas – enquanto UNION ALL retorna todas as linhas, incluindo as duplicadas. Avalie a possibilidade de usar UNION ALL ao invés de UNION. Apesar de retornar mais linhas, é menos custoso, pois não tem o trabalho de remover as linhas duplicadas.

Ruim (usando UNION):

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBTRUxFQ1QgcmVnaW9uX2lkLCByZWdpb25fbmFtZQogIDIgIEZST00gcmVnaW9ucwogIDMgIFVOSU9OCiAgNCAgU0VMRUNUIHJlZ2lvbl9pZCwgcmVnaW9uX25hbWUKICA1ICBGUk9NIHJlZ2lvbnMyOwoKIFJFR0lPTl9JRCBSRUdJT05fTkFNRQotLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KICAgICAgICAgMSBFdXJvcGUKICAgICAgICAgMiBBbWVyaWNhcwogICAgICAgICAzIEFzaWEKICAgICAgICAgNCBNaWRkbGUgRWFzdCBhbmQgQWZyaWNh” hover_enabled=”0″]U1FMPiBTRUxFQ1QgcmVnaW9uX2lkLCByZWdpb25fbmFtZQogIDIgIEZST00gcmVnaW9ucwogIDMgIFVOSU9OCiAgNCAgU0VMRUNUIHJlZ2lvbl9pZCwgcmVnaW9uX25hbWUKICA1ICBGUk9NIHJlZ2lvbnMyOwoKIFJFR0lPTl9JRCBSRUdJT05fTkFNRQotLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KICAgICAgICAgMSBFdXJvcGUKICAgICAgICAgMiBBbWVyaWNhcwogICAgICAgICAzIEFzaWEKICAgICAgICAgNCBNaWRkbGUgRWFzdCBhbmQgQWZyaWNh[/et_pb_dmb_code_snippet]

Bom (usando UNION ALL):

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBTRUxFQ1QgcmVnaW9uX2lkLCByZWdpb25fbmFtZQogIDIgIEZST00gcmVnaW9ucwogIDMgIFVOSU9OIEFMTAogIDQgIFNFTEVDVCByZWdpb25faWQsIHJlZ2lvbl9uYW1lCiAgNSAgRlJPTSByZWdpb25zMjsKCiBSRUdJT05fSUQgUkVHSU9OX05BTUUKLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCiAgICAgICAgIDEgRXVyb3BlCiAgICAgICAgIDIgQW1lcmljYXMKICAgICAgICAgMyBBc2lhCiAgICAgICAgIDQgTWlkZGxlIEVhc3QgYW5kIEFmcmljYQogICAgICAgICAxIEV1cm9wZQogICAgICAgICAyIEFtZXJpY2FzCiAgICAgICAgIDMgQXNpYQogICAgICAgICA0IE1pZGRsZSBFYXN0IGFuZCBBZnJpY2E=” hover_enabled=”0″]U1FMPiBTRUxFQ1QgcmVnaW9uX2lkLCByZWdpb25fbmFtZQogIDIgIEZST00gcmVnaW9ucwogIDMgIFVOSU9OIEFMTAogIDQgIFNFTEVDVCByZWdpb25faWQsIHJlZ2lvbl9uYW1lCiAgNSAgRlJPTSByZWdpb25zMjsKCiBSRUdJT05fSUQgUkVHSU9OX05BTUUKLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCiAgICAgICAgIDEgRXVyb3BlCiAgICAgICAgIDIgQW1lcmljYXMKICAgICAgICAgMyBBc2lhCiAgICAgICAgIDQgTWlkZGxlIEVhc3QgYW5kIEFmcmljYQogICAgICAgICAxIEV1cm9wZQogICAgICAgICAyIEFtZXJpY2FzCiAgICAgICAgIDMgQXNpYQogICAgICAgICA0IE1pZGRsZSBFYXN0IGFuZCBBZnJpY2E=[/et_pb_dmb_code_snippet]