How to reduce Mysql query execution time?

Asked

Viewed 98 times

1

In a Mysql database 5.6 I have two tables, TAB_A and TAB_B, whose relation is from 1 registry in TAB_A to "n" records in TAB_B. I am making an appointment as indicated below. When the TAB_A table has more than 10,000 records and TAB_B has more than 200,000 records, the query is very slow (it takes at least 15 seconds). I am using in the tests a Windows PC with I7 processor and 8GB of RAM. There are no other systems occupying memory or consuming CPU processing.

QUESTION: How to optimize this query to get the same result in less time?

Table: TAB_A

ID | DATAHORA
Onde: 
    ID = index, primary key, inteiro
    DATAHORA: Datetime

TAB_B table:

ID | ID_TAB_A | REF | VALOR
Onde: 
    ID = index, primary key, inteiro
    ID_TAB_A = index, inteiro
    REF: VARCHAR(100)
    VALOR: VARCHAR(255)

Consultation:

SELECT    
    TAB_B.REF,
    TAB_B.VALOR,    
    Max(TAB_A.DATAHORA) as DtTime  
FROM    
    TAB_B LEFT JOIN TAB_A 
        ON TAB_A.ID = TAB_B.ID_TAB_A
GROUP BY
    TAB_B.REF  
ORDER BY TAB_B.REF
  • Already tested creating an index on the field ID_TAB_A of the table TAB_B? Already ran a EXPLAIN?

  • The ID_TAB_A of the TAB_B table already has an index, as indicated in the row ID_TAB_A = index, inteiro

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.