# How to improve the speed of mysql query using count(\*) **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [April 23, 2007, 5:02am UTC](https://forums.percona.com/t/how-to-improve-the-speed-of-mysql-query-using-count/297 "2007-04-23T05:02:08Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![julian](https://avatars.discourse-cdn.com/v4/letter/j/74df32/32.png) [@julian](https://forums.percona.com/u/julian)\ **Post date:** [April 23, 2007, 5:02am UTC](https://forums.percona.com/t/how-to-improve-the-speed-of-mysql-query-using-count/297/1 "2007-04-23T05:02:08Z")
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 ---
**Author:** ![Gregd](https://avatars.discourse-cdn.com/v4/letter/g/a183cd/32.png) [@Gregd](https://forums.percona.com/u/Gregd)\ **Post date:** [April 30, 2007, 1:45pm UTC](https://forums.percona.com/t/how-to-improve-the-speed-of-mysql-query-using-count/297/2 "2007-04-30T13:45:19Z")
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. ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [April 30, 2007, 4:00pm UTC](https://forums.percona.com/t/how-to-improve-the-speed-of-mysql-query-using-count/297/3 "2007-04-30T16:00:37Z")
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. ---
**Author:** ![julian](https://avatars.discourse-cdn.com/v4/letter/j/74df32/32.png) [@julian](https://forums.percona.com/u/julian)\ **Post date:** [May 1, 2007, 10:51pm UTC](https://forums.percona.com/t/how-to-improve-the-speed-of-mysql-query-using-count/297/4 "2007-05-01T22:51:16Z")
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 ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [May 2, 2007, 4:44am UTC](https://forums.percona.com/t/how-to-improve-the-speed-of-mysql-query-using-count/297/5 "2007-05-02T04:44:11Z")
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. ---
**Author:** ![julian](https://avatars.discourse-cdn.com/v4/letter/j/74df32/32.png) [@julian](https://forums.percona.com/u/julian)\ **Post date:** [May 4, 2007, 12:22am UTC](https://forums.percona.com/t/how-to-improve-the-speed-of-mysql-query-using-count/297/6 "2007-05-04T00:22:18Z")
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 ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [May 4, 2007, 3:42am UTC](https://forums.percona.com/t/how-to-improve-the-speed-of-mysql-query-using-count/297/7 "2007-05-04T03:42:06Z")
1. Just to check, is this true?: | [B]Quote:[/B] | | DB size is 3.3 MB | It seems so incredibly small. 1. 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 |