Professional Web Applications Themes

slow select count(*) - MySQL

I am doing a "select count(*) mem_address from mem_address;" on a table with 750,000 rows. It takes over six seconds to return the first time. The subsequent executions of SQL come back instantaneously because of query caching. Any ideas why a count(*) would be slow and how I can speed it up? Thanks!...

  1. #1

    Default slow select count(*)

    I am doing a "select count(*) mem_address from mem_address;" on a table with
    750,000 rows. It takes over six seconds to return the first time. The
    subsequent executions of SQL come back instantaneously because of query
    caching.

    Any ideas why a count(*) would be slow and how I can speed it up?

    Thanks!


    Rajesh Guest

  2. #2

    Default Re: slow select count(*)

    On Apr 23, 5:22 pm, "Rajesh Kapur" <org> wrote: 


    Try just counting the primary key field instead:

    SELECT COUNT(id) FROM mem_address

    strawberry Guest

  3. #3

    Default Re: slow select count(*)

    The first time response time for "SELECT COUNT(id) FROM mem_address" is the
    same as "select count(*) mem_address from mem_address;"
    This is an innodb table.

    "strawberry" <com> wrote in message
    news:googlegroups.com... 
    >
    >
    > Try just counting the primary key field instead:
    >
    > SELECT COUNT(id) FROM mem_address
    >[/ref]


    Rajesh Guest

  4. #4

    Default Re: slow select count(*)

    the most efficient way to do this is have a separate table with the counts in it.
    you then put a trigger on the innodb table mem_address that updates the count
    table every time there is an insert or delete
    lark Guest

  5. #5

    Default Re: slow select count(*)

    Rajesh Kapur schrieb: 

    This *should* be near-instantaneous.
    Try to YZE TABLE mem_address.

    Possibly there is a typo in the SQL you're using; please copy&paste the
    slow query both into whatever your frontend is and the message.

    Does the table have a primary key? It's possible that InnoDB doesn't
    keep track of the number of rows other than in the primary key.

    Regards,
    Jo
    Joachim Guest

  6. #6

    Default Re: slow select count(*)

    Joachim Durchholz wrote :
     
    >
    > This *should* be near-instantaneous.
    > Try to YZE TABLE mem_address.
    >
    > Does the table have a primary key? It's possible that InnoDB doesn't
    > keep track of the number of rows other than in the primary key.[/ref]

    AFAIK, even for the PK InnoDB does not keep track of the exact number of
    rows. Just make a
    "show index from foo" to look at the cardinality of your PK and then a
    "select count(1) from foo" and you should not have exaclty the same
    result (unless lucky).

    For the Original Poster, according to me the delay is caused by the
    loading of the index data from disk to memory.

    I do have a table ("bar") with around 10 millions of entries. With a
    MySQL just restarde. Memory print of MySQL is around 150 MB. I just then do
    "select count(1) from bar;"
    that take around 60 seconds to execute. At the end of the process, the
    MySQL memory print is around 4G, just because the index of the table bar
    was loaded into memory. I did, for other reasons, some YZE on this
    table without change on this part.

    If just afeter that (once the index are into memory) I do again a
    "select sql_no_cache count(1) from bar;" it takes only 1 or 2 seconds.
    (You should use the "sql_no_cache" statement to avoid MySQL using the
    query cache, and get relevant result).

    So if you really need this information, and need it quickly, as someone
    else state before, explicitly store the number of elements in
    "mem_adress" somewhere else. But this number will be more or less
    accurate, since with InnoDB engine is transactional and use some
    isolation policy. I guess this is why the number of rows per table is
    not stored directly by the InnoDB engine unlike MyISAM.

    --
    Hugo
    Hugo Guest

  7. #7

    Default Re: slow select count(*)

    Hugo schrieb: 

    Ah, right, now I recall.
    This is actually mentioned in the mysql doentation somewhere (I think
    on the driver-specific page for InnoDB).

    Regards,
    Jo
    Joachim Guest

Similar Threads

  1. how can i use SELECT COUNT for this?
    By Dan in forum ASP Database
    Replies: 11
    Last Post: September 11th, 07:57 PM
  2. extracting value of select(count) in the API
    By Steven in forum Informix
    Replies: 2
    Last Post: January 23rd, 04:31 PM
  3. a select within a count ??
    By andrewst in forum Oracle Server
    Replies: 3
    Last Post: July 1st, 01:33 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