At one time, I developed a page of reports, the users have a filters option, this is working, but as the data in the database are growing it is getting slower and I do not know which option is best to solve this problem, some of the things I thought were:
-
If it is always better to bring the data already calculated by the query, or if in some cases, it is better to return all the data, and do these calculations using php, for example?
-
Am I creating the querys correctly? What I mean by this, I'm doing the accounts in several sub-querys, here is an example:
SELECT DISTINCT t.nome_tag, ( SELECT count(vw.tag_id) FROM view_relatorio vw WHERE vw.tag_id = t.id AND vw.cliente_id IN (1,2,3,10,20) AND vw.conforme = 1 AND vw.dt_validate >= '2016-09-09' ) as conforme, ( SELECT count(vw.tag_id) FROM view_relatorio vw WHERE vw.tag_id = t.id AND vw.cliente_id IN (1,2,3,10,20) AND vw.conforme = 0 ) as naoConforme, ( SELECT count(vw.tag_id) FROM view_relatorio vw WHERE vw.tag_id = t.id AND vw.cliente_id IN (1,2,3,10,20) ) as totalAtribuido FROM view_relatorio view JOIN tag t ON t.id = view.tag_id WHERE view.cliente_id IN (1,2,3,10,20)
Remembering that this is a small example and has several other querys.
I have already built views to improve, as I have read in some places, I have already indexed columns in the necessary tables, so in order to improve the part of the database I have made several adjustments.