MongoDB LIKE vs. Mysql LIKE

Question

I am moving some parts of a site from mysql to mongodb, and one part that will use mongodb is search. There I have one problem, if I make this query in mysql

SELECT * FROM table WHERE a1 LIKE '%test%' OR a2 LIKE '%test%'

I will get over 10k results. That's ok.

If I do this query in mongo

$mongo->find(
    array(
        '$or' => array(
            array('a1' => new MongoRegex('/test/')),
            array('a2' => new MongoRegex('/test/'))
        )
    )
)->count();

I will get around 2k results, 5x fewer. How is that happening?

The data in each db is the same.

Answer 1

MySQL's LIKE is case-insensitive by default:

The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a binary string:

mysql> SELECT 'abc' LIKE 'ABC';
   -> 1

But MongoDB's regex searches will be case-sensitive by default because JavaScript's regexes are case sensitive.

Try using a case-insensitive regex. I'm not familiar with MongoRegex's syntax but the fine manual says that the usual /i modifier should work:

new MongoRegex('/test/i')

The case issue is consistent with getting a lot more matches in MySQL (~10k) than MongoDB (~2k).