Re: Is there any way to tell the optimizer that my large, but simple-schema table are already sorted?

[Rick James]

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.