Lookup against MYSQL TEXT type column

Question

My table/model has TEXT type column, and when filtering for the records on the model itself, the AR where produces the correct SQL and returns correct results, here is what I mean :

MyNamespace::MyValue.where(value: 'Good Quality')

Produces this SQL :

SELECT `my_namespace_my_values`.* 
FROM `my_namespace_my_values` 
WHERE `my_namespace_my_values`.`value` = '\\\"Good Quality\\\"'

Take another example where I m joining MyNamespace::MyValue and filtering on the same value column but from the other model (has relation on the model to my_values). See this (query #2) :

OtherModel.joins(:my_values).where(my_values: { value: 'Good Quality' })

This does not produce correct query, this filters on the value column as if it was a String column and not Text, therefore producing incorrect results like so (only pasting relevant where) :

WHERE my_namespace_my_values`.`value` = 'Good Quality'

Now I can get past this by doing LIKE inside my AR where, which will produce the correct result but slightly different query. This is what I mean :

OtherModel.joins(:my_values).where('my_values.value LIKE ?, '%Good Quality%')

Finally arriving to my questions. What is this and how it's being generated for where on the model (for text column type)?

WHERE `my_namespace_my_values`.`value` = '\\\"Good Quality\\\"'

Maybe most important question what is the difference in terms of performance using :

WHERE `my_namespace_my_values`.`value` = '\\\"Good Quality\\\"'

and this :

(my_namespace_my_values.value LIKE '%Good Quality%')

and more importantly how do I get my query with joins (query #2) produce where like this :

WHERE `my_namespace_my_values`.`value` = '\\\"Good Quality\\\"'

Answer 1

(Partial answer -- approaching from the MySQL side.)

What will/won't match

Case 1: (I don't know where the extra backslashes and quotes come from.)

WHERE `my_namespace_my_values`.`value` = '\\\"Good Quality\\\"'

\"Good Quality\"               -- matches
Good Quality                   -- does not match
The product has Good Quality.  -- does not match

Case 2: (Find Good Quality anywhere in value.)

WHERE my_namespace_my_values.value LIKE '%Good Quality%'

\"Good Quality\"               -- matches
Good Quality                   -- matches
The product has Good Quality.  -- matches

Case 3:

WHERE `my_namespace_my_values`.`value` = 'Good Quality'

\"Good Quality\"               -- does not match
Good Quality                   -- matches
The product has Good Quality.  -- does not match

Performance:

• If value is declared TEXT, all cases are slow.
• If value is not indexed, all are slow.
• If value is VARCHAR(255) (or smaller) and indexed, Cases 1 and 3 are faster. It can quickly find the one row, versus checking all rows.

Phrased differently:

• LIKE with a leading wildcard (%) is slow.
• Indexing the column is important for performance, but TEXT cannot be indexed.