Help Optimize MySql Query

I hope this is the right category to post this question.

I use Visual Basic 6 and MySql. Some queries are slow and the database doesn't have more than 1200 records yet.

I make this simple query and it displays it in the grid.

Code:

---

"SELECT * FROM table1 ORDER BY num ASC"

---

Then, if I want to display a column containing a sum of the records linked to each of these elements, I need to open another separate query.

Code:

---

"SELECT COUNT(DISTINCT(id_registro)) as QTDE FROM table2 WHERE id_planoconta=" & id

---

Does anyone suggest a single MySql query for this situation?

You can combine both queries in one so called correlated query like this

"SELECT *, (SELECT COUNT(DISTINCT(id_registro)) as QTDE FROM table2 WHERE id_planoconta=table1.id) AS CountOfRegistro FROM table1 ORDER BY num ASC"

The green part is what used to be your first query, the red part is the correlated second query.

cheers,
</wqw>

Huh?

Untested

Code:

---

SELECT T1.ID, T1.SomeField, COUNT(DISTINCT(T2.id_registro)) AS QTDE
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T2.id_planoconta=T1.ID
GROUP BY T1.ID, T1.SomeField
ORDER BY T1.num ASC

---

Or with CTE

Code:

---

WITH
  CTE AS (SELECT id_planoconta, COUNT(DISTINCT(id_registro)) as QTDE
          FROM table2 GROUP BY id_planoconta)

SELECT T1.*, CTE.QTDE
FROM TAble1 AS T1
LEFT JOIN CTE ON CTE.id_planoconta=T1.ID
ORDER BY T1.num ASC

---

I don't know MySQL, but I would expect that it has indexes. Do you have indexes on the relevant fields? The only relevant field from your example appears to be id_planoconta. If you do have an index, is it badly fragmented? With so few records, it might not matter much, but badly fragmented indexes can wreck query performance.

Quote:

---

Originally Posted by Shaggy Hiker

I don't know MySQL, but I would expect that it has indexes. Do you have indexes on the relevant fields? The only relevant field from your example appears to be id_planoconta. If you do have an index, is it badly fragmented? With so few records, it might not matter much, but badly fragmented indexes can wreck query performance.

---

That field is probably a foreign key to table1 and has no automatic index.
but agreed: create an index consisting of both fields: foreign and primary key

wqweto

I tried your solution, but the CountOfRegistro returns empty.
I haven't tried the other suggestions yet, I didn't have time today.
As soon as I test them, I'll comment on the results here with you. Thanks for now.

Zvoni

I had never heard of CTE. Interesting, I'll try it as soon as possible and comment later. Tks

Caminhante peludo (kkkk)

Já tinha lido sobre índices, mas nunca coloquei em prática. Não sei ao certo se atenderia ao meu propósito, mas vou tentar esta opção com certeza. Valeu

Quote:

---

Originally Posted by ricardoweb084

Zvoni

I had never heard of CTE. Interesting, I'll try it as soon as possible and comment later. Tks

---

CTE (=Common Table Expression) is something like a "View" but created on the fly and not stored in the DB as an object

AFAIK, CTE In MySQL requires MySQL8 or higher

EDIT: A slight bit corrected

Code:

---

WITH
  CTE AS (SELECT id_planoconta, COUNT(DISTINCT(id_registro)) as QTDE
          FROM table2 GROUP BY id_planoconta)

SELECT T1.*, COALESCE(CTE.QTDE,0 ) AS QTDE
FROM TAble1 AS T1
LEFT JOIN CTE ON CTE.id_planoconta=T1.ID
ORDER BY T1.num ASC

---

Everything said: Going by his nomenclature i'd hazard a guess, that
1) id_planoconta in Table2 is a Foreign Key to ID of Table1
2) id_registro is the Primary Key in Table2 --> This is a guess

Meaning: He's querying everything from table 1, and wants the Count of how many Child-records in Table2 exist for each Record in Table 1
The only thing not making sense is the use of DISTINCT in COUNT, provided id_registro IS actually the primary key

One thing to consider is focusing on index optimization for both id_planoconta and id_registro. If id_planoconta is a foreign key and id_registro is the primary key in table2, creating a composite index on these fields could potentially speed up your query significantly. This would help improve performance, especially when counting records in table2.

Quote:

---

Originally Posted by ricardoweb084

wqweto

I tried your solution, but the CountOfRegistro returns empty.
I haven't tried the other suggestions yet, I didn't have time today.
As soon as I test them, I'll comment on the results here with you. Thanks for now.

---

Obviously there are no rows in Table2 with populated *id_planoconta* values that match any of the *id* values in Table1.

You cannot JOIN tables on random columns. Your foreign keys has to reference exact primary key. The idea is *all* of the values in Table2.id_planoconta to be available in Table1.id so that no value in Table2.id_planoconta is not found in Table1.id.

Again, check if id_planoconta is a foreign key to Table1 or just a random column which you cannot use to JOIN anything.

cheers,
</wqw>