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

MySQL & MariaDB Other MySQL® Questions
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

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.

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.

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

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.

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

7

Just to check, is this true?:

[B]Quote:[/B]

DB size is 3.3 MB

It seems so incredibly small.

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:

[B]Quote:[/B]

marking1_ix_personname_persondate

Then I know immediately what this index does.
  1. 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:

[B]Quote:[/B]

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