Sean Forman wrote:
Got this fixed. Tried this query.
mysql> select * from image where img_name like "%JohnMcGraw.jpg";
1 row in set (0.01 sec)
mysql> select * from image where img_name like "JohnMcGraw.jpg%";
1 row in set (0.00 sec)
mysql> select * from image where img_name like "JohnMcGraw.jpg";
0 row in set.
That seems to be a broken index. The third query uses the index, the
first one doesn't use it. I don't remember if the second query qualifies
for the index, but I'm pretty sure it does. It seems that you got some
junk after the img_name field in the index, and queries fail unless you
avoid the index (first query) or ignore the trailing (second query).
The SQL query 'REPAIR TABLE img_name;', or the command 'myisamchk
--recover' would fix this problem for you, or...
mysql> alter table image add column img_name2
mysql> update image set img_name2=img_name;
mysql> select * from image where img_name2 like "JohnMcGraw.jpg";
1 row in set (0.00 sec
mysql> update image set img_name=img_name2;
mysql> alter table image drop column img_name2;
You pretty much rebuilt the index manually, in that forth statement. I
think that even an "update image set img_name=img_name;" would do it in
Juliano F. Ravasi ·· http://juliano.info/
5105 46CC B2B7 F0CD 5F47 E740 72CA 54F4 DF37 9E96
"A candle loses nothing by lighting another candle." -- Erin Majors
* NOTE: Don't try to reach me through this address, use "contact@" instead.