Carga pesada em uma consulta simples

Question

Existem duas tabelas:

maindata = id, devid, value (linhas de 10M) djournal = id, devid, md_id_begin, md_id_end, state (10k linhas)

Eu quero selecionar todos de maindata para determinados devid , exceto as linhas com state :

incorretas

SELECT md.* 
  FROM maindata AS md
  LEFT JOIN djournal AS dj
    ON md.id BETWEEN dj.md_id_begin AND dj.md_id_end
    AND md.devid = dj.devid
  WHERE md.devid = 123456789
    AND dj.state <> 'idle'
  ORDER BY md.id ASC;

Dada a consulta, produza exatamente o que eu quero, mas sloooooow. Todos os índices possíveis foram criados. Claro que é fácil armazenar o campo state diretamente na tabela maindata , mas é curioso por que essa consulta é tão lenta e existe alguma solução?

    

Answer 1

Desculpe por perturbação, pessoas, não existe solução para esse problema. Isso não é um problema, é um comportamento normal do mecanismo sql. Eu tentei explicar porque. Vamos ter dois conjuntos:

mysql> select * from Q;      mysql> select * from R;
+----+------+                +----+------+
| id | val  |                | id | val  |
+----+------+                +----+------+
|  1 | a    |                |  1 | a    |
|  2 | b    |                |  2 | b    |
|  3 | c    |                |  3 | c    |
|  4 | d    |                |  4 | d    |
|  5 | e    |                |  5 | e    |
+----+------+                +----+------+

Vamos fazer um JOIN sem condição:

mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R;
+------+------+
| Qval | Rval |
+------+------+
| a    | a    |
| b    | a    |
| c    | a    |
| d    | a    |
| e    | a    |
| a    | b    |
| b    | b    |
| c    | b    |
| d    | b    |
| e    | b    |
| a    | c    |
| b    | c    |
| c    | c    |
| d    | c    |
| e    | c    |
| a    | d    |
| b    | d    |
| c    | d    |
| d    | d    |
| e    | d    |
| a    | e    |
| b    | e    |
| c    | e    |
| d    | e    |
| e    | e    |
+------+------+
25 rows in set (0.00 sec)

Vamos endireitar JOIN pela condição "=":

mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R ON Q.val = R.val;
+------+------+
| Qval | Rval |
+------+------+
| a    | a    |
| b    | b    |
| c    | c    |
| d    | d    |
| e    | e    |
+------+------+
5 rows in set (0.00 sec)

E quando nos inscrevemos em ">" nós temos:

mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R ON Q.val > R.val;
+------+------+
| Qval | Rval |
+------+------+
| b    | a    |
| c    | a    |
| d    | a    |
| e    | a    |
| c    | b    |
| d    | b    |
| e    | b    |
| d    | c    |
| e    | c    |
| e    | d    |
+------+------+
10 rows in set (0.00 sec)

Condição negligente produz resultado negligente. Condição complexa reduz o conjunto resultante, mas aumenta significativamente a quantidade de cálculos. Quando nos juntamos entre ou < ou > nós obtemos enormes tabelas temporárias para resultados intermediários - sem índices, pesquisados pelo filesort.

Portanto, unir conjuntos por algo diferente de "=" - é uma má ideia.

    

Answer 2

Você acabou de ter um problema de indexação.

Você não publicou a estrutura do banco de dados, mas se você fizer essa pergunta, é porque você não sabe muito sobre bancos de dados (porque todo servidor de banco de dados decente pode mostrar onde a consulta passa seu tempo).

Seus índices ausentes provavelmente estão em md_id_begin , md_id_end e state . Apenas um palpite.
Indexar id também poderia ser uma boa ideia se você não o fez.