Professional Web Applications Themes

[PHP] mysql Pattern Matching - PHP Development

From: "Ralph Guzman" <ralph213sbcglobal.net> > Is there an advantage or difference in running FIND_IN_SET() instead of > LIKE? Actually no, I guess there isn't. Neither one will use an index. mysql> desc test; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | b | varchar(50) | | MUL | | | +-------+-------------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> explain select * from test where b like '%two%'; +-------+--------+---------------+------+---------+------+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+------+---------+------+------+-------+ | test | system | NULL ...

  1. #1

    Default Re: [PHP] mysql Pattern Matching

    From: "Ralph Guzman" <ralph213sbcglobal.net>

    > Is there an advantage or difference in running FIND_IN_SET() instead of
    > LIKE?
    Actually no, I guess there isn't. Neither one will use an index.

    mysql> desc test;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | b | varchar(50) | | MUL | | |
    +-------+-------------+------+-----+---------+-------+
    1 row in set (0.01 sec)

    mysql> explain select * from test where b like '%two%';
    +-------+--------+---------------+------+---------+------+------+-------+
    | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +-------+--------+---------------+------+---------+------+------+-------+
    | test | system | NULL | NULL | NULL | NULL | 1 | |
    +-------+--------+---------------+------+---------+------+------+-------+
    1 row in set (0.00 sec)

    mysql> explain select * from test where find_in_set('two',b);
    +-------+--------+---------------+------+---------+------+------+-------+
    | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +-------+--------+---------------+------+---------+------+------+-------+
    | test | system | NULL | NULL | NULL | NULL | 1 | |
    +-------+--------+---------------+------+---------+------+------+-------+
    1 row in set (0.00 sec)

    ---John Holmes...
    Cpt John W. Holmes Guest

  2. #2

    Default Re: [PHP] mysql Pattern Matching

    * Thus wrote CPT John W. Holmes (holmes072000charter.net):
    > From: "Ralph Guzman" <ralph213sbcglobal.net>
    >
    >
    > > Is there an advantage or difference in running FIND_IN_SET() instead of
    > > LIKE?
    >
    > Actually no, I guess there isn't. Neither one will use an index.
    There will be a slight diffence in the resuls though, depending on
    the term searched and the contents

    id field
    --- ------
    1 one,two
    2 fooone,footwo
    3 onefoo,twofoo
    >
    > mysql> explain select * from test where b like '%two%';
    select id from test where b like '%two%'

    id
    ---
    1
    2
    3
    > mysql> explain select * from test where find_in_set('two',b);
    select id from test where find_in_set('two',b);

    id
    ---
    1


    Curt
    --
    "I used to think I was indecisive, but now I'm not so sure."
    Curt Zirzow Guest

Similar Threads

  1. pattern matching
    By Tad McClellan in forum PERL Beginners
    Replies: 30
    Last Post: June 23rd, 07:42 PM
  2. Pattern matching for xx-xx-xx string
    By jeff@nospam.com in forum PHP Development
    Replies: 7
    Last Post: January 19th, 06:38 PM
  3. Pattern matching username
    By perl@swanmail.com in forum PERL Beginners
    Replies: 1
    Last Post: September 30th, 05:37 PM
  4. matching the pattern (strings)
    By MJS in forum PERL Beginners
    Replies: 0
    Last Post: September 28th, 07:04 PM
  5. mysql Pattern Matching
    By Electroteque in forum PHP Development
    Replies: 3
    Last Post: September 4th, 03:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139