MySQL INNER JOIN - '=' vs 'like'

Question

We're having some odd issues with MySQL inner joins. Basically, we get an odd error when using an '=' operator but using 'like' instead makes it work. Unfortunately, this is via ActiveRecord and no easy way to just slap 'like' in there instead, plus we want to understand what's actually happening here.

Here is the query that fails:

mysql> SELECT COUNT(*) FROM `versions` INNER JOIN `site_versions`
              ON `versions`.id = `site_versions`.version_id;

Here is the error:

ERROR 1296 (HY000): Got error 20008 'Query aborted due to out of query memory'
from NDBCLUSTER

Here is the query that works:

mysql> SELECT COUNT(*) FROM `versions` INNER JOIN `site_versions`
              ON `versions`.id like `site_versions`.version_id;

Here are some details on the tables themselves:

mysql> desc site_versions;
+----------------------+----------+------+-----+---------+----------------+
| Field                | Type     | Null | Key | Default | Extra          |
+----------------------+----------+------+-----+---------+----------------+
| id                   | int(11)  | NO   | PRI | NULL    | auto_increment |
| version_id           | int(11)  | YES  | MUL | NULL    |                |
[..snip..]
+----------------------+----------+------+-----+---------+----------------+

mysql> desc versions;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
[..snip..]
+------------+--------------+------+-----+---------+----------------+

Any ideas why the 'like' works and the '=' does not?

Answer 1

Ironically this seems to be related to optimizations; by using LIKE you force MySQL to stop using possible indexes (at least with number columns, because it has to cast them all to string for the comparison).

So by using = it seems that MySQL simply runs out of space (memory / disc) to use the index (check the key_buffer setting).

Of course, this is just a hunch and I don't know much about NDB to help you further, but I hope this moves you in the right direction.