Results 1 to 11 of 11

Thread: Help Optimize MySql Query

  1. #1

    Thread Starter
    Junior Member ricardoweb084's Avatar
    Join Date
    Mar 2024
    Posts
    18

    Post 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?

  2. #2
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,540

    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>

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,801

    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

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    39,457

    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

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,801

    Re: Help Optimize MySql Query

    Quote Originally Posted by Shaggy Hiker View Post
    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

  6. #6

    Thread Starter
    Junior Member ricardoweb084's Avatar
    Join Date
    Mar 2024
    Posts
    18

    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.

  7. #7

    Thread Starter
    Junior Member ricardoweb084's Avatar
    Join Date
    Mar 2024
    Posts
    18

    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

  8. #8

    Thread Starter
    Junior Member ricardoweb084's Avatar
    Join Date
    Mar 2024
    Posts
    18

    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

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,801

    Re: Help Optimize MySql Query

    Quote Originally Posted by ricardoweb084 View Post
    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

  10. #10
    New Member
    Join Date
    Oct 2024
    Posts
    11

    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.

  11. #11
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,540

    Re: Help Optimize MySql Query

    Quote Originally Posted by ricardoweb084 View Post
    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
  •  



Click Here to Expand Forum to Full Width