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?
– anonimo
The
ID_TAB_A
of the TAB_B table already has an index, as indicated in the rowID_TAB_A = index, inteiro
– wBB