Professional Web Applications Themes

Trying to use a unique index on a large varchar field. - MySQL

Hi, I'm trying to create a table that contains a field for websites using a VARCHAR(500). Furthermore, I would like to define this field as UNIQUE. This by itself is not a problem; mysql will let me do this. The problem comes when I decided I would also like this field to permit fast lookups by creating an index on this field. Because the field is so big, I can only index on some initial subset of the field, which is fine, like an INDEX website_name(100) or something. However, at this point, it seems that mysql will no longer let ...

  1. #1

    Default Trying to use a unique index on a large varchar field.

    Hi,

    I'm trying to create a table that contains a field for websites using a
    VARCHAR(500). Furthermore, I would like to define this field as
    UNIQUE. This by itself is not a problem; mysql will let me do this.
    The problem comes when I decided I would also like this field to permit
    fast lookups by creating an index on this field.

    Because the field is so big, I can only index on some initial subset of
    the field, which is fine, like an INDEX website_name(100) or something.
    However, at this point, it seems that mysql will no longer let me
    declare the field as unique, only the index, but that is too strong a
    constraint for me -- website_name(100) might not be unique.

    It seems to me that there are two ways that UNIQUE is being used by
    mysql here -- as a constraint, and as an optimization. I just want the
    constraint so that I can easily make sure websites don't get entered
    twice. I don't actually need the extra speed that would come from
    defining the index as UNIQUE.

    So is there any way to do this? To say that upon insertions, mysql
    should ensure that no other entry exists with the same data, but then
    to index it in the best way possible, even if it means just a plain old
    index, and not a unique index?

    Thanks very much.

    Nathan Guest

  2. #2

    Default Re: Trying to use a unique index on a large varchar field.

    Nathan Addy wrote: 

    Actually, UNIQUE is only a constraint, not an optimization. But MySQL,
    like some other RDB's, enforces the constraint via an index. And, as
    you noted, this won't work for you.

    You should be able to do it in a BEFORE trigger, though.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Trying to use a unique index on a large varchar field.

    Wow, I'm surprised by that (UNIQUE is only a constraint, not an
    optimization). It seems like it would be relatively easy to use it for
    various kinds of optimizations.

    Maybe the most obvious would come when mysql is asked to lookup on two
    non-key, non-indexed fields, one being unique and the other not.
    Without thinking about it too heavily, it would seem that in both
    cases, about all mysql can do is scan through every table entry,
    collecting the keys that have the appropriate field. In the unique
    case, mysql can quit as soon as it finds the entry, so that it only has
    to scan through some fraction of the table. In the non-unique case, it
    seems like mysql would have to scan the entire table, no matter what
    finds during the scanning process.

    For indexing, the speedups seem less obvious, but at least you could
    probably save a handful of pointer dereferences (for a unique index,
    finding the index entry could return a pointer to the location of the
    record directly, as opposed to a a pointer to a set of pointers, each
    of which point to a location of a record -- maybe it's judged that one
    single extra pointer dereference would be no better than one fewer,
    plus keeping track of whether that field should be dereferenced once or
    twice).

    Anyway, thanks for the advice. Being that I'm a relative mysql/trigger
    newbie, I wonder if you would be so kind as to check my trigger syntax.
    Presumably I'd want to add something like the following...

    CREATE TABLE webpages (
    id INTEGER UNSIGNED AUTO_INCREMENT,
    page_name VARCHAR(500) NON NULL,
    PRIMARY KEY(id),
    INDEX page_name(100)
    );

    CREATE webpage_unique_trigger BEFORE INSERT ON webpages
    FOR EACH ROW
    BEGIN
    IF COUNT(select id from webpages where page_name=NEW.page_name)>0 THEN
    CALL ERROR_NON_UNIQUE_PAGENAME();
    END IF;
    END;

    It's kind of hacky, but would it work? Obviously what I'm trying to do
    here is to check whether the entry I'm entering exists already, and if
    so, call a non existent function to crash the thing and raise an error,
    which can be caught by my program.

    Thanks,
    Nathan




    Jerry Stuckle wrote: 
    >
    > Actually, UNIQUE is only a constraint, not an optimization. But MySQL,
    > like some other RDB's, enforces the constraint via an index. And, as
    > you noted, this won't work for you.
    >
    > You should be able to do it in a BEFORE trigger, though.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    Nathan Guest

  4. #4

    Default Re: Trying to use a unique index on a large varchar field.

    Nathan Addy wrote: 
    >>
    >>Actually, UNIQUE is only a constraint, not an optimization. But MySQL,
    >>like some other RDB's, enforces the constraint via an index. And, as
    >>you noted, this won't work for you.
    >>
    >>You should be able to do it in a BEFORE trigger, though.
    >>
    >>--
    >>==================
    >>Remove the "x" from my email address
    >>Jerry Stuckle
    >>JDS Computer Training Corp.
    >>net
    >>==================[/ref]
    >
    >[/ref]
     
    (Top posting fixed)

    Well, first of all, there's a difference between a constraint and an
    optimization. Constraints limit what can be placed in the database.
    For instance, you might set up a constraint that a test score must be
    between 0 and 100, inclusive. Foreign keys are another type of constraint.

    An optimization, OTOH, is something which doesn't affect what data can
    be inserted; rather it affects affects the performance on at least one
    operation (i.e. SELECT) - and hopefully the most common operation(s) :-).

    Now, MySQL implements uniqueness via a unique index (a primary key is
    just a special case of a unique index). So it could easily be argued
    that this is both a constraint and an optimization by virtue of the way
    it is implemented (which is, BTW, a common way of implementing unique
    constraints).

    As for your trigger - it looks OK to me, but I'm not an expert on MySQL
    triggers. My previous experience with triggers was with DB2.

    However, right now there isn't any way to actually cause an exception to
    abort the trigger in MySQL. I assume that's what your CALL statement is
    doing. Another way (one recommended in the forums) is to set a non-null
    column to null.

    Also, I believe you need NEW.page_name in single quotes (') since it's a
    string.

    Also, don't forget you'll need an UPDATE trigger to handle the case
    where someone changes a URI to something which already exists.


    P.S. Please don't top post.
    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Unique Index
    By Alex in forum PostgreSQL / PGSQL
    Replies: 30
    Last Post: January 20th, 09:00 PM
  2. converting unique index into primary key
    By Ed L. in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 23rd, 03:42 AM
  3. #25544 [Ver->Bgs]: Only 255 characters returned from varchar field
    By abies@php.net in forum PHP Development
    Replies: 0
    Last Post: September 16th, 09:37 AM
  4. #25544 [Opn->Bgs]: Only 255 characters returned from varchar field
    By iliaa@php.net in forum PHP Development
    Replies: 0
    Last Post: September 15th, 10:32 PM
  5. Updating a Long VarChar field
    By Gustavo Brown in forum Informix
    Replies: 2
    Last Post: August 16th, 11:25 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