Query taking too long time - 【StackMirror】|mysql

MySql query taking too long time to give result

Select pty_code from sis.tblBf a where a.BFno 
       not in (select b.BNo from sislatest.tbltransaction b)

note: i am comparing two different database and getting the difference.

Select A.pty_code from DataBase1.TableName A 
       where A.BFno NOT IN (SELECT B.BNo From DataBase2.TableName B)

mysql

2012-04-04 07:02
by Sensa

Need a LOT more information. How long is 'too long'? How are your tables defined? What indices are on the tables? How much data is in each table - Eric J. 2012-04-04 07:04

Please get into the habit of giving your table aliases that represent the table. eg. sis.tblBf should be aliased as 'bf' not 'a', you'll thank me in 6 months time - Toby Allen 2012-04-04 07:27

Perhaps an indexing issue? This is where the mysql explain syntax comes in hand:

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

Other things that may help would be to 'optimize table', or even 'analyze table' to check for inconsistency.

2012-04-04 07:08
by Rob

Using a subselect, especially with a "not in" clause is going to be slow. I'd recommend rewriting to a left outer join with an "is null" in the where clause.

SELECT a.pty_code from sis.tblBf a LEFT JOIN sislatest.tbltransaction b on b.BNo = a.BFno WHERE b.BNo IS NULL

I think that would work but without the database to test against, I'm writing it blind.

If that's still slow, I'd take a look and make sure there are indices on b.BNo and A.BFNo.

2012-04-04 07:26
by David Stockton

Can you explain the last keys: WHERE b.BNo IS NULL What it mean actually - Sensa 2012-04-04 16:19

A left join combines all the rows from the first table and matches up with the rows in the the second table. If the second table doesn't have any matches, those fields will have NULL as the value. In SQL NULL is not equal to anything else (even NULL) so you have to specify IS NULL instead of something like b.BNo = null - David Stockton 2012-04-05 04:58