#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    37
    Rep Power
    2

    Query getting stuck "copying to tmp table"


    I'm running this query in phpmyadmin but it won't complete - it gets stuck on "loading". MySQL says "copying to tmp table" and it stays like that for hours. Eventually it stops but there is no result. The table has about 100,000 records.

    SELECT
    *
    FROM
    client_invoices
    WHERE
    (ownerid, invoice_date) IN (
    SELECT
    ownerid
    , invoice_date
    FROM
    client_invoices
    GROUP BY
    ownerid
    , invoice_date
    HAVING
    COUNT(*) > 1
    )
    ORDER BY
    ownerid DESC
    , invoice_date DESC;

    Can anyone tell me what I need to do to get this query to run? I have access to the my.cnf
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Rewrite the query so that it uses a derived table instead of a sub query in the WHERE clause:
    Code:
    SELECT
      ci.*
    FROM
    client_invoices ci
    INNER JOIN
      (
        SELECT
        ownerid
        , invoice_date
        FROM
        client_invoices
        GROUP BY
        ownerid
        , invoice_date
        HAVING
        COUNT(*) > 1
      ) ci_duplicates
      ON ci.ownerid = ci_duplicates.ownerid AND ci.invoice_date=ci_duplicates.invoice_date
    ORDER BY
    ownerid DESC
    , invoice_date DESC;
    The problem with a sub-query in MySQL is that it is executed once for _each_ row in the outer query, so placing a heavy query with GROUP BY in the sub-query will result in poor performance.
    Especially when a value is not injected from the outer query so that the sub query will return all values every time.

    But by rewriting the query as a derived table used in a join the sub query will only be executed once.

    And to speed up both the outer and inner query an index like this is suggested:
    Code:
    ALTER TABLE client_invoices ADD INDEX clientinvoices_ix_ownerid_invoicedate (ownerid, invoice_date);

    Comments on this post

    • chris74 agrees : Just what I needed
    /Stefan
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    37
    Rep Power
    2
    That's perfect - thank you!

IMN logo majestic logo threadwatch logo seochat tools logo