MySQL Forums
Forum List  »  Optimizer & Parser

Re: Is there any way to tell the optimizer that my large, but simple-schema table are already sorted?
Posted by: Rick James
Date: November 28, 2011 11:06AM

In a few cases, ORDER BY NULL prevents a sort pass. (But probably not in your case.)

That query does not check for the tables being identical. If you have one row in one table and zero rows in the other, that query will return zero.

Do you have a compound index on (a,b)? If not, then it will be inefficient.

If any of the fields are NULL, you might get the "wrong" count.

Are these tables MyISAM or InnoDB? What is the PRIMARY KEY, or do you not have one? If it is InnoDB and you do not have a PRIMARY KEY, adding one on (a,b) would actually save disk space.

Something like this:
SELECT  BIT_OR(t), a,b,c
    FROM  
      ( SELECT  1 as t, a,b,c  FROM  X
        UNION 
        SELECT  2 as t, a,b,c  FROM  Y
      ) z
    GROUP BY  a,b,c
    HAVING  BIT_OR(t) != 3;
will not only tell you if there are differences, it will also tell you if there are extra/missing rows, and tell you which table has what value.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Is there any way to tell the optimizer that my large, but simple-schema table are already sorted?
796
November 28, 2011 11:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.