•  

Announcement

Announcement Module
Collapse
No announcement yet.

How to improve the speed of mysql query using count(*)

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to improve the speed of mysql query using count(*)

    #1
    Hi

    I'm using this kind of queries in mysql in InnoDB engine



    Select count(*) from marking1 where persondate between '2007-04-23 00:00:00.000' and '2007-04-23 23:59:59.999' and PersonName='aaa'




    While executing these queries from front end VB, It takes above 5 secs with 50 thousand records.


    How can I improve speed for this kind of queries. Is there any alternation for this command.


    Any one knows , Explain

    thanks
    Tags: None

  • #2
    Count(*) are slow on Innodb ... You need a Myisam table for it to be fast or you need to use another table and make a counter on that I think.

    Comment


    • #3
      Actually a COUNT(*) without any _WHERE condition_ is much faster on MyISAM since it stores the total nr of rows in the table in the table header.

      But a COUNT(*) with a where condition like in your case needs a proper index to gain speed both on MyISAM and InnoDB.

      What indexes do you have on the table?

      My suggestion is a combined index on (PersonName, persondate) because then both columns in your WHERE condition is part of the index and that is the optimum.

      Comment


      • #4
        Thanks for ur reply


        S.I created index on both column names personname,persondate


        The result of Explain stmt is


        id: 1
        select_type: SIMPLE
        table: marking1
        type: range
        possible_keys: IX_Marking1
        key: IX_Marking1
        key_len: 57
        ref: NULL
        rows: 1
        Extra: Using where; Using index




        If we increase RAM size ,will the query run fast?.



        Any other method to increase the speed of the query?


        Thanks

        Comment


        • #5
          How large is your DB in MB?

          How much memory do you have free on the server?

          What is your my.cnf settings?


          The reason for these questions is that if all of the index and table is already cached in RAM then more RAM will not make a difference.
          But if the DB is larger than the available RAM on the machine then you can benefit from adding more RAM.

          Comment


          • #6
            Hi


            thanks for ur information


            In my server


            DB size is 3.3 MB


            Memory free on the server is 9 GB


            My.cnf settings are below


            #log-bin

            #server-id = 1

            long_query_time =1
            log-slow-queries =/var/log/mysql/mysql-slow-queries.log

            query_cache_type = 2
            query_cache_size = 26214400


            I'm using 512 MB RAM, If I increase RAM size, Will query run fast?


            What is the reason for slow queries while using select count(*) ... where condition. I created index also.




            Thanks

            Comment


            • #7
              1.
              Just to check, is this true?:
              Quote:


              DB size is 3.3 MB


              It seems so incredibly small.

              2.
              I just want to make sure,
              did you really create a combined index or did you create two indexes, one each column?

              I usually name my indexes like:
              table_ix_col1_col2
              Then I know which columns that are part of the index by just looking at the name.
              In your case my index would be named:
              Quote:


              marking1_ix_personname_persondate


              Then I know immediately what this index does.

              3. my.cnf
              That was a very small my.cnf.

              And that means that you don't have almost any internal caching configured since MySQL is very conservative with the default values.
              Here's a couple of addtions to your my.conf, just the most important ones for InnoDB:
              Quote:


              innodb_buffer_pool_size = 64M
              innodb_additional_mem_pool_size = 8M
              innodb_log_file_size = 20M
              innodb_log_buffer_size = 32M
              innodb_flush_log_at_trx_commit = 1

              Comment

              Working...
              X