Categories

JavaScriptPHPHTMLCssPythonWordPressWeb developmentLinuxMySQLAndroidWindowsJavaC++ / C#LayoutReactUbuntuYiiDjangoProgramming1C-BitrixLaravelSystem administrationTelegramJSONDebianPostgreSQLIOSGoogleHtaccessWindows ServerGitMacOSGoogle ChromeCMSWooСommerceBootstrapNginxSQLUnityAPI
Make a leaderReport
D
D
dNertyco2021-10-08 18:00:49
SQL query optimization
dNertyco, 2021-10-08 18:00:49

How can this sql code be optimized?

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

Answer the question

In order to leave comments, you need to log in

1 answer(s)
Report
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

Similar questions
A
Andrey Ovsyankin2014-03-14 16:21:41
Which query plan is better? 3Reply
H
Hosting Yaroslavl2018-12-05 13:41:34
Does the size of an adjacent table in a MySQL database affect the performance of queries against a single small table? 2Reply
I
Ivanoff-i2016-11-30 11:22:51
How can you speed up a low-selectivity fetch from a table with hundreds of millions of records? 2Reply
I
Igor Bykov2016-05-16 12:05:51
Why doesn't the search form run the script? 5Reply
A
AlexeewAlex2018-10-25 21:05:16
Not simple filters in Laravel. Is there a solution? 3Reply

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question