Как можно оптимизировать данный sql код?

Question

[dNertyco]

Сейчас выполнение данного кода на выдачу 5и результатов составляет 10 секунд, а нужно намного меньше, но что же делать?

Упрощённый код:

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

Полный код:

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

Comments

[Алексей Горбунов]

Лентюй, MySQL же, нет?

[dNertyco]

Алексей Горбунов, MySQL

[dNertyco]

Лентюй, план чего?

[Алексей Горбунов]

dNertyco, план запроса же, нет?

[dNertyco]

Алексей Горбунов, если удобно текстом - то вот:
выбрать всё из таблицы table_sql
далее оставить только те результаты, которые одинаковы по нескольким значениям (INNER JOIN)
отсортировать (не столь важно)

[dNertyco]

Алексей Горбунов, хочу отметить, что 0 и 1 в запросе нужны,но не влияют на сам запрос, это нужно для генерации данного кода

[Алексей Горбунов]

dNertyco, Для получения плана запроса надо поставить EXPLAIN перед SELECT и выполнить запрос.
Тебе сервер MySQL выдаст информацию. Покажи нам, что тебе сервер сказал по поводу твоего запроса.

Answer 1

[Алексей Горбунов]

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

---- три строчки --- поэтому выносим их за OR без последствий ------

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

В итоге имеем что-то более менее вменяемое

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