[Bug 1047105] Re: btree index not working for strings that start with the % char

[Clint Byrum]

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