Select COUNT very slow

Discussion in 'MySQL' started by deriklogov, Oct 9, 2009.

0
  1. #1
    Select COUNT very slow
    any alternative to that ?
     
    deriklogov, Oct 9, 2009 IP
  2. mwasif

    mwasif Active Member

    Messages:
    816
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #2
    Create the appropriate indexes.
     
    mwasif, Oct 11, 2009 IP
  3. apache

    apache Member

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #3
    you should show full mysql query, db structure and description of records then, we can try to help you...
     
    apache, Oct 12, 2009 IP
  4. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #4
    If your are using MySql/InnoDB, since it has the ACID properties it has to check the table rows visible within its own transaction to find the answer, otherwise you would loose the ACID properties. The same goes for Postgresql.

    MySql /MyISAM uses a shortcut using a single field in a table to mark the number of rows in the table, which is a single lookup which is faster. Now you can guess why some people say MyIsam is faster. It is faster for this specific operation that is used on websites, since it does not give you ACID properties.
     
    chisara, Oct 12, 2009 IP
  5. phones2me

    phones2me Peon

    Messages:
    35
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    phones2me, Oct 14, 2009 IP
  6. alexpr07

    alexpr07 Active Member

    Messages:
    284
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    73
    #6
    What are you counting? Records? You might wanna use Recordset.RecordCount
     
    alexpr07, Oct 15, 2009 IP
  7. deriklogov

    deriklogov Well-Known Member

    Messages:
    1,079
    Likes Received:
    22
    Best Answers:
    0
    Trophy Points:
    130
    #7
    Recordset is that part of mysql ?
     
    deriklogov, Oct 15, 2009 IP
  8. phones2me

    phones2me Peon

    Messages:
    35
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #8
    no it's part of the Windows Data Access Components SDK
     
    phones2me, Oct 16, 2009 IP
  9. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #9
    If the Recordset.RecordCount works like the recordsets I know then then the recordset is a client side list with entries which are the result of your query. And gues where the resultset comes from, yep the DB server.
    So instead of a simple query in to the DB server your are doing a select all records, then transferring them and then asking client side how many there are. Sorry this sucks in terms of memory and bandwidth. and does not solve the question of why the count is so slow.

    Again I ask the very SIMPLE question what table type are you using deriklogoc myisam or innodb or an other ?
     
    chisara, Oct 19, 2009 IP