Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Question Unanswered: Optimize mysql join with 3 tables

    Hello,

    I need some help with optimizing this mysql query code:

    Code:
    SELECT tblinvoices.id, tblinvoices.userid, tblinvoices.datepaid, tblinvoices.total, tblinvoices.credit, tblclients.companyname, tblclients.firstname, tblclients.lastname, tblclients.currency AS clvaluta
    FROM tblinvoices
    LEFT JOIN racuni ON tblinvoices.id = racuni.pid
    LEFT JOIN tblclients ON tblinvoices.userid = tblclients.id
    WHERE NOT EXISTS (SELECT * FROM racuni WHERE racuni.pid = tblinvoices.id)
    AND tblinvoices.paymentmethod = 'tco'
    AND tblinvoices.status = 'Paid'
    AND tblinvoices.date > '2011-01-01'
    ORDER BY tblinvoices.datepaid ASC;

    It's working like expected, but it's very slow, about 5-6 minutes to finish.

    Is there any other way to write this query so it will finish much faster?

    Thanks to all,
    Miron J.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    optimizing a query requires ensuring that the appropriate indexes for that query have been defined

    however, in some cases, the query itself has some nonsense in it, and this is one of those cases

    remove this line --
    Code:
    LEFT JOIN racuni ON tblinvoices.id = racuni.pid
    just remove it completely and see what happens
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    3
    Hi,

    On every tables are added indexes on "id", "pid" or "userid" fields.

    Removing line
    Code:
    LEFT JOIN racuni ON tblinvoices.id = racuni.pid
    does not help, almost same result:

    Before removing:
    ~~~~~~~~~~~~~~
    2 rows in set (5 min 45.83 sec)
    ~~~~~~~~~~~~~~

    After removing:
    ~~~~~~~~~~~~~~
    2 rows in set (5 min 47.29 sec)
    ~~~~~~~~~~~~~~

    Main issue is in line:
    Code:
    WHERE NOT EXISTS (SELECT * FROM racuni WHERE racuni.pid = tblinvoices.id)
    when I remove this, query is executed very fast:
    ~~~~~~~~~~~~~~
    17729 rows in set (0.72 sec)
    ~~~~~~~~~~~~~~

    but then query return all rows, and I need only rows where "racuni.pid" are not same as "tblinvoices.id"


    Thanks,
    Miron J.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What does EXPLAIN SELECT return? This will give an idea to the indexes that are being used.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MironJ View Post
    when I remove this, query is executed very fast:
    aha!!

    okay, please try this --
    Code:
    SELECT tblinvoices.id
         , tblinvoices.userid
         , tblinvoices.datepaid
         , tblinvoices.total
         , tblinvoices.credit
         , tblclients.companyname
         , tblclients.firstname
         , tblclients.lastname
         , tblclients.currency AS clvaluta
      FROM tblinvoices
    LEFT OUTER
      JOIN tblclients 
        ON tblclients.id = tblinvoices.userid
    LEFT OUTER
      JOIN racuni 
        ON racuni.pid = tblinvoices.id
     WHERE tblinvoices.paymentmethod = 'tco'
       AND tblinvoices.status = 'Paid'
       AND tblinvoices.date > '2011-01-01'
       AND racuni.pid IS NULL
    ORDER 
        BY tblinvoices.datepaid ASC;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2011
    Posts
    3

    Thumbs up

    Hi,

    Just tried, it's working just as I need, and it's fast:
    ~~~~~~~~~~~~~~~~~
    2 rows in set (13.05 sec)
    ~~~~~~~~~~~~~~~~~

    Thank you very much!

    Miron J.

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
  •