How can this sql code be optimized?

D

dNertyco2021-10-08 18:00:49

SQL query optimization

dNertyco, 2021-10-08 18:00:49

Now the execution of this code to issue 5 results is 10 seconds, but it takes much less, but what to do?

Simplified code:

SELECT 
  * 
FROM 
  table_sql AS t0 
  INNER JOIN table_sql AS t1 ON (
    t0.id <> t1.id 
    AND (
      0 
      OR (
        1 
        AND t0.P1_01 = t1.P1_01 
        AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10 
        AND t0.P2_01 = t1.P2_01
      ) 
      OR (
        1 
        AND t0.P1_01 = t1.P1_01 
        AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10
      ) 
      OR (
        1 
        AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10 
        AND t0.P2_01 = t1.P2_01
      ) 
    )
  ) 
ORDER BY 
  t0.date ASC 
LIMIT 
  5

Full code:

SELECT 
  * 
FROM 
  table_sql AS t0 
  INNER JOIN table_sql AS t1 ON (
    t0.id <> t1.id 
    AND (
      0 
      OR (
        1 
        AND t0.P1_01 = t1.P1_01 
        AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10 
        AND t0.P2_01 = t1.P2_01
      ) 
      OR (
        1 
        AND t0.P1_01 = t1.P1_01 
        AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10
      ) 
      OR (
        1 
        AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10 
        AND t0.P2_01 = t1.P2_01
      ) 
      OR (
        1 
        AND t0.P1_10 = t1.P1_10 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10 
        AND t0.X_20 = t1.X_20 
        AND t0.P2_10 = t1.P2_10
      ) 
      OR (
        1 
        AND t0.P1_10 = t1.P1_10 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10 
        AND t0.X_20 = t1.X_20
      ) 
      OR (
        1 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10 
        AND t0.X_20 = t1.X_20 
        AND t0.P2_10 = t1.P2_10
      ) 
      OR (
        1 
        AND t0.P1_01 = t1.P1_01 
        AND t0.X_02 = t1.X_02 
        AND t0.X_10 = t1.X_10 
        AND t0.P2_01 = t1.P2_01
      ) 
      OR (
        1 
        AND t0.P1_01 = t1.P1_01 
        AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10
      ) 
      OR (
        1 
        AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10 
        AND t0.P2_01 = t1.P2_01
      ) 
      OR (
        1 
        AND t0.P1_10 = t1.P1_10 
        AND t0.X_01 = t1.X_01 
        AND t0.X_20 = t1.X_20 
        AND t0.P2_10 = t1.P2_10
      ) 
      OR (
        1 
        AND t0.P1_10 = t1.P1_10 
        AND t0.X_01 = t1.X_01 
        AND t0.X_20 = t1.X_20
      ) 
      OR (
        1 
        AND t0.X_01 = t1.X_01 
        AND t0.X_20 = t1.X_20 
        AND t0.P2_10 = t1.P2_10
      ) 
      OR (
        1 
        AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10
      ) 
      OR (
        1 
        AND t0.P1_01 = t1.P1_01 
        AND t0.X_01 = t1.X_01 
        AND t0.P2_01 = t1.P2_01
      ) 
      OR (
        1 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10 
        AND t0.X_20 = t1.X_20
      ) 
      OR (
        1 
        AND t0.P1_10 = t1.P1_10 
        AND t0.X_10 = t1.X_10 
        AND t0.P2_10 = t1.P2_10
      ) 
      OR (
        1 
        AND t0.X_01 = t1.X_01 
        AND t0.X_02 = t1.X_02 
        AND t0.X_03 = t1.X_03
      ) 
      OR (
        1 
        AND t0.X_10 = t1.X_10 
        AND t0.X_20 = t1.X_20 
        AND t0.X_30 = t1.X_30
      )
    )
  ) 
ORDER BY 
  t0.date ASC 
LIMIT 
  5

Reply

Answer the question

In order to leave comments, you need to log in

1 answer(s)

A

Alexey Gorbunov, 2021-10-08
@leha_gorbunov

SELECT 
  * 
FROM 
  table_sql AS t0 
  INNER JOIN table_sql AS t1 ON (
    t0.id <> t1.id 
    AND (
      0

----- From here -------

OR (
        1 
        AND t0.P1_01 = t1.P1_01 
        AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10 
        AND t0.P2_01 = t1.P2_01
      )

---- Until here --- DO NOT FUCK---- because this is a union of the next two blocks and does not affect the request----

OR (
        1 
        AND t0.P1_01 = t1.P1_01

----These three lines-----

AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10

)
OR (
1
----- exactly the same as these-----

AND t0.X_02 = t1.X_02 
        AND t0.X_01 = t1.X_01 
        AND t0.X_10 = t1.X_10

---- three lines --- therefore we take them out after OR without consequences ------

AND t0.P2_01 = t1.P2_01
      ) 
    )
  ) 
ORDER BY 
  t0.date ASC 
LIMIT 
  5

As a result, we have something more or less sane

SELECT 
  * 
FROM 
  table_sql AS t0 
  INNER JOIN table_sql AS t1 ON 
    t0.id <> t1.id 
    AND t0.X_02 = t1.X_02 
    AND t0.X_01 = t1.X_01 
    AND t0.X_10 = t1.X_10
    AND ( t0.P1_01 = t1.P1_01  OR  t0.P2_01 = t1.P2_01)
ORDER BY 
  t0.date ASC 
LIMIT 
  5