Professional Web Applications Themes

optimizing where clauses - MySQL

Hi, So I have several tables in a mysql db, which are keyed by my users' email address. I ran various selects and so on, based on email. Right now the DB is tiny, but I want to make sure I don't run into problems later. My question is, does it make a difference whether the where clause is run against a string like an email address or an integer ID? I read the section on optimizing where clauses in the mysql manual, and I realize indexes are the way to go. Just wondering if the type of data makes ...

  1. #1

    Default optimizing where clauses

    Hi,
    So I have several tables in a mysql db, which are keyed by my
    users' email address. I ran various selects and so on, based on email.
    Right now the DB is tiny, but I want to make sure I don't run into
    problems later.

    My question is, does it make a difference whether the where clause
    is run against a string like an email address or an integer ID? I read
    the section on optimizing where clauses in the mysql manual, and I
    realize indexes are the way to go. Just wondering if the type of data
    makes a difference.

    I'm using Mysql 4.x, and unfortunately have to do so until my
    webhoster upgrades.

    Sandman

    Sandman Guest

  2. #2

    Default Re: optimizing where clauses

    Sandman wrote: 

    Sandman,

    Comparing integers is a single assembler instruction. Comparing strings
    requires a multiple-instruction loop. Which do you think would be faster?

    And how much faster depends on the length of the string, whether the
    char field is defined as binary (case sensitive) or not (not case
    sensitive - extra instructions) and how many comparisons must be done.

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

  3. Moderated Post

    Default Re: optimizing where clauses

    Removed by Administrator
    Beauregard Guest
    Moderated Post

  4. #4

    Default Re: optimizing where clauses

    Absolutely right and I should've known before posting.

    Thanks for the other suggestion regarding email addresses as keys.
    Luckily its not too late to go rethink this (and maybe get someone's
    help :-)

    Thanks,
    Sandman

    On Feb 5, 2:57 pm, Jerry Stuckle <net> wrote: 



    >
    > Sandman,
    >
    > Comparing integers is a single assembler instruction. Comparing strings
    > requires a multiple-instruction loop. Which do you think would be faster?
    >
    > And how much faster depends on the length of the string, whether the
    > char field is defined as binary (case sensitive) or not (not case
    > sensitive - extra instructions) and how many comparisons must be done.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]


    Sandman Guest

Similar Threads

  1. Replies: 10
    Last Post: October 2nd, 06:17 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