Hi Joseph, thanks for taking the time to file this bug report and help make Ubuntu better!
Your test code (and perhaps app code) is not escaping % properly. %% at the beginning of a LIKE clause will still be treated like a wildcard followed by %. MySQL's manual shows that you need to escape all %'s in literals with \% http://dev.mysql.com/doc/refman/5.5/en/string-literals.html If I change your code to escape the %'s both examples run in the same amount of time, And EXPLAIN on one of the queries shows the difference: mysql> explain SELECT fileUUID FROM btreeTest1 WHERE currentLocation LIKE '%%transferDirectory%%objects/Maildir/INBOX/cur/1344899423_0.10003.bion,U=689,FMD5=%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: btreeTest1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 16920 Extra: Using where 1 row in set (0.00 sec) mysql> explain SELECT fileUUID FROM btreeTest1 WHERE currentLocation LIKE '\%transferDirectory\%objects/Maildir/INBOX/cur/1344899423_0.10003.bion,U=689,FMD5=%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: btreeTest1 type: range possible_keys: currentLocation key: currentLocation key_len: 770 ref: NULL rows: 5 Extra: Using where 1 row in set (0.00 sec) mysql> explain SELECT fileUUID FROM btreeTest2 WHERE currentLocation LIKE '*transferDirectory*objects/Maildir/INBOX/cur/1344899423_0.10003.bion,U=689,FMD5=%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: btreeTest2 type: range possible_keys: currentLocation key: currentLocation key_len: 770 ref: NULL rows: 5 Extra: Using where 1 row in set (0.00 sec) Its worth noting that this b-tree is an extremely inefficient way to search this data anyway, as every row, no matter how long the currentLocation fields, will create a 767 byte key in the b-tree. You may find that splitting that field up into dir/base and indexing only a few bytes of each one will provide a much better search experience. Anyway, there is no bug here, so I'll close this bug. If you feel there is more information that I have missed, please feel free to re-open the bug to status New or open a new bug report. ** Changed in: mysql-5.5 (Ubuntu) Status: New => Invalid -- You received this bug notification because you are a member of Ubuntu Server Team, which is subscribed to mysql-5.5 in Ubuntu. https://bugs.launchpad.net/bugs/1047105 Title: btree index not working for strings that start with the % char To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/mysql-5.5/+bug/1047105/+subscriptions -- Ubuntu-server-bugs mailing list [email protected] Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-server-bugs
