Professional Web Applications Themes

is limit 1 needed on a query on and auto_increment or unique column - MySQL

Say I have a simple table with an ordinary auto_increment column called id. Is MySQL smart enough to know that a query like select * from mytab where id=123 will have only one match, or is it always a good idea to append limit 1 to such queries? If mysql knows (and my guess is that it probably does), then does adding limit 1 actually do anything to slow down the query? The same question would apply to a table with a column defined with the UNIQUE constraint....

  1. #1

    Default is limit 1 needed on a query on and auto_increment or unique column

    Say I have a simple table with an ordinary auto_increment column called
    id.
    Is MySQL smart enough to know that a query like

    select * from mytab where id=123

    will have only one match, or is it always a good idea to append limit 1
    to such queries? If mysql knows (and my guess is that it probably
    does),
    then does adding limit 1 actually do anything to slow down the query?

    The same question would apply to a table with a column defined with
    the UNIQUE constraint.

    lynn.newton@gmail.com Guest

  2. #2

    Default Re: is limit 1 needed on a query on and auto_increment or uniquecolumn

    com wrote: 

    Unless this column is the table's primary key or has a unique index,
    even an autoincrement column can have duplicates.

    Autoincrement is only the default. You can still specify your own
    values on an insert or update statement.

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

  3. #3

    Default Re: is limit 1 needed on a query on and auto_increment or unique column

    > Say I have a simple table with an ordinary 
     
     
     
     
     

    You are right. But suppose it *is* a primary key or has
    a unique index, is adding "limit 1" superfluous, i.e.,
    does MySQL know to stop searching for matches after
    finding one because it knows by the table definition
    that there can't be any more?

    lynn.newton@gmail.com Guest

  4. #4

    Default Re: is limit 1 needed on a query on and auto_increment or unique column

    On 20 Dec 2006 06:56:29 -0800, com wrote: 





    >
    > You are right. But suppose it *is* a primary key or has
    > a unique index, is adding "limit 1" superfluous, i.e.,
    > does MySQL know to stop searching for matches after
    > finding one because it knows by the table definition
    > that there can't be any more?[/ref]

    In this case, MySQL is *probably* going to use that index to find the
    value, and will reach the end of its saught values very, very quickly.
    While LIMIT 1 *might* be even faster, I don't think it'll provide a
    difference of more than academic interest.

    --
    Cunningham's First Law:
    Any sufficiently complex deterministic system will exhibit
    non-deterministic behaviour.
    Peter Guest

  5. #5

    Default Re: is limit 1 needed on a query on and auto_increment or uniquecolumn

    com wrote: 



    >

    >

    >
    >
    > You are right. But suppose it *is* a primary key or has
    > a unique index, is adding "limit 1" superfluous, i.e.,
    > does MySQL know to stop searching for matches after
    > finding one because it knows by the table definition
    > that there can't be any more?
    >[/ref]

    It will stop searching for matches because there is only one in the
    primary (or unique) index. It won't find any more.

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

Similar Threads

  1. Count unique data in column
    By kev in forum MySQL
    Replies: 10
    Last Post: June 2nd, 08:51 PM
  2. Select after insert to the unique column
    By Bruno Wolff III in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: December 13th, 04:44 PM
  3. Access (ROWID) Unique ID from Datagrid Column
    By Mona Syed in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: June 1st, 02:52 PM
  4. counting number of unique entries under a column
    By mr_burns in forum PHP Development
    Replies: 2
    Last Post: October 9th, 07:29 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