Optimize mysql join with 3 tables

MironJ · #1 (**permalink**) 11-02-11, 09:37

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.

r937 · #2 (**permalink**) 11-02-11, 10:10

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

MironJ · #3 (**permalink**) 11-02-11, 13:16

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.

it-iss.com · #4 (**permalink**) 11-02-11, 13:56

What does EXPLAIN SELECT return? This will give an idea to the indexes that are being used.

r937 · #5 (**permalink**) 11-02-11, 14:01

Quote:

Originally Posted by MironJ

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;

MironJ · #6 (**permalink**) 11-02-11, 14:09

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech