How to optimize sql query?

Question

How to optimize sql query?

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (-2 votes)

0

I happen to have a table with n records ... 20 for example ... Of those 20, 5 records belong to 4 people, that is to say that there are 4 people who were registered 5 times in that table, for a total of 20 (by way of example) ... The query that I must obtain is only 4 records, and correspond to the last reigstro of each person, taking into account that this table has a column start date where the value of the date varies in ascending order. I have the following sql code:

SELECT * FROM MYTABLA T1 WHERE T1.fechainicio=(SELECT MAX(fechainicio) FROM MYTABLA WHERE idpersona=T1.idpersona)

As you can see, I do 2 things (select's) to the same table .. Is there any way to improve that query? Well there will be thousands of records ... In addition to that I also do JOINS to other 3 tables ..

Editing my question for a better explanation ....

The first SELECT with the JOINs makes everything perfect, obtaining a result like this: It generates a result of 9 records, the table has 10 but there is a user who is 2 times and only brings the last one of that user ...

On the other hand, the query that @LCC gives me generates all 10 records like this:

I understand that using subqueries within the where slows the query, but I do not know how to remove that subquery from the where in such a way that I keep bringing the same results I need (the last of each user), and thus optimize the query.

sql postgresql

asked by Nelson Sepulveda 22.02.2018 в 16:28

source

3 answers

1

If the rows that correspond to a single person are only differentiated by the starting date field, the following query serves you.

If you need more fields, with informing in the SELECT and in the GROUP BY the fields that are the same for the same person you should be able to get the information you need, and for the fields that are different you can use the formulas (MAX, MIN , COUNT, etc).

 SELECT idpersona, MAX(fechainicio)
    FROM MYTABLA T1
    GROUP BY idpersona

answered by 22.02.2018 в 16:40

-2

SELECT pg.*, pg.valorplan-sum(p.valorabonado) as debe, sum(p.valorabonado) as totalpagado, count(*) as pagosrealizados, 
       (u.nombre || ' ' || u.apellido) as nombres, u.cedula, u.telefono, pl.nombreplan, e.nombreempleado, em.nombreempleado as empleado_actualizado
 FROM pagoperiodicos pg
 LEFT JOIN pagos p ON p.idpago=pg.idpago 
 LEFT JOIN planes pl ON pg.idplan=pl.idplan
 INNER JOIN empleado e ON e.idempleado=pg.idempleado 
 LEFT JOIN empleado em ON em.idempleado=pg.idempleadoactualizado 
 INNER JOIN usuario u ON u.idusuario=pg.idusuario 
 INNER JOIN (
 	SELECT idusuario, max(fechainicio) as maxima FROM pagoperiodicos GROUP BY idusuario
 ) as R
 ON pg.idusuario=R.idusuario AND pg.fechainicio=R.maxima
 GROUP BY pg.idpago, pl.idplan, u.idusuario, e.idempleado, em.idempleado
 ORDER BY fechaderegistro DESC

answered by 22.02.2018 в 18:39

Problem with MySQL privileges Improve the performance of a collection with multiple records

score 1 · Accepted Answer

The answer I was developing is exactly the same as what you have arrived at, I simply answer you because I add some more information that may eventually be useful. What you are looking for you can solve it like this:

SELECT  pg.* 
    FROM pagoperiodicos pg 
    INNER JOIN (SELECT  idusuario, 
                        MAX(fechainicio) as fechainicio
                        FROM pagoperiodicos 
                        GROUP BY idusuario
        ) pg2 
        ON pg.idusuario = pg2.idusuario
        AND pg.fechainicio = Pg2.fechainicio

In Spanish: We retrieve the maximum date of pagoperiodicos by idusuario and simply a JOIN between table and subquery. We can not remove the subquery from above, but we use it in a way that is usually more optimal, I usually say, because the "ways of the engine are inscrutable." Surely this query would be very grateful if fechainicio has an index.

Now, is it necessary to do it this way? and maybe not, but it depends on the columns that you are finally going to show. If you repeat rows but the columns you are going to show always have the same values, you can directly do GROUP BY of all the columns and solve the issue, I think that's what @gbianchi points to in his comments.

Another important clarification is that this form will only work if fechainicio is a unique data, in practice only the autonuméricos are, so if we could say that idpago is related to the seniority of the records, we could replace MAX(fechainicio) with MAX(idpago) .