SELECT Without wildcard returns 0 rows For some reason my SELECT query with an AND clause is exhibiting some odd behaviour. It returns zero rows, when I know for a fact that there are matches. If I remove either of the clauses, it works. Or, the strangest part, if I add a wildcard to the end of the string to match, it returns the expected results. Even though there doesn't appear to be any extra bytes at the end of the column data.

A demonstration:

SELECT HEX(type),
       type,
       category 
  FROM cats 
 WHERE `category` = 10 AND 
       `type` LIKE "Furry";

Empty set (0.07 sec)


but

SELECT HEX(type),type,category FROM cats WHERE `type` LIKE "Furry";
+----------------------------------+------------------------+----------+
| HEX(type)                        | type                   | category |
+----------------------------------+------------------------+----------+
| 4675727279                       | Furry                  |       10 |
...


And

SELECT HEX(type),type,category FROM cats WHERE `category`=10 AND `type` LIKE "Furry%";
+----------------------------------+------------------------+----------+
| HEX(type)                        | type                   | category |
+----------------------------------+------------------------+----------+
| 4675727279                       | Furry                  |       10 |
...


What am I missing here?
Based on the execution plan, and the data distribution, MySQL would decide to scan the table or one of the indexes to get the required data.

If the data is to be read from an index, and the index is corrupted, it is possible that no data or wrong data is returned. 

Optimizing the table will rebuild the table and its indexes. So, if an index was corrupted, it will be fixed after the optimization.

HTH