-
Oct 8th, 2024, 11:02 PM
#1
Thread Starter
Junior Member
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?
-
Oct 9th, 2024, 06:02 AM
#2
Re: Help Optimize MySql Query
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>
-
Oct 9th, 2024, 06:59 AM
#3
Re: Help Optimize MySql Query
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Oct 9th, 2024, 10:44 AM
#4
Re: Help Optimize MySql Query
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.
My usual boring signature: Nothing
-
Oct 9th, 2024, 11:09 AM
#5
Re: Help Optimize MySql Query
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Oct 9th, 2024, 09:24 PM
#6
Thread Starter
Junior Member
Re: Help Optimize MySql Query
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.
-
Oct 9th, 2024, 09:25 PM
#7
Thread Starter
Junior Member
Re: Help Optimize MySql Query
Zvoni
I had never heard of CTE. Interesting, I'll try it as soon as possible and comment later. Tks
-
Oct 9th, 2024, 09:27 PM
#8
Thread Starter
Junior Member
Re: Help Optimize MySql Query
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
-
Oct 10th, 2024, 01:57 AM
#9
Re: Help Optimize MySql Query
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
Last edited by Zvoni; Oct 10th, 2024 at 02:01 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Oct 16th, 2024, 07:43 AM
#10
New Member
Re: Help Optimize MySql Query
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.
-
Oct 16th, 2024, 10:13 AM
#11
Re: Help Optimize MySql Query
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>
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|