MySQL Performance: MySQL => How to improve the speed of mysql query using count(*)

Members

Help

Home

Home » Performance » MySQL » How to improve the speed of mysql query using count(*)

Show: Today's Messages :: Show Polls :: Message Navigator

How to improve the speed of mysql query using count(*) [message #1134]

Mon, 23 April 2007 06:32

julian
Messages: 13
Registered: March 2007

Junior Member

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

Report message to a moderator

Re: How to improve the speed of mysql query using count(*) [message #1171 is a reply to message #1134 ]

Mon, 30 April 2007 15:15

Gregd
Messages: 1
Registered: April 2007

Junior Member

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.

Report message to a moderator

Re: How to improve the speed of mysql query using count(*) [message #1174 is a reply to message #1134 ]

Mon, 30 April 2007 17:30

sterin
Messages: 323
Registered: March 2007
Location: Sweden

Senior Member

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.

[Updated on: Mon, 30 April 2007 17:31]

Report message to a moderator

Re: How to improve the speed of mysql query using count(*) [message #1180 is a reply to message #1174 ]

Wed, 02 May 2007 00:21

julian
Messages: 13
Registered: March 2007

Junior Member

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

Report message to a moderator

Re: How to improve the speed of mysql query using count(*) [message #1182 is a reply to message #1134 ]

Wed, 02 May 2007 06:14

sterin
Messages: 323
Registered: March 2007
Location: Sweden

Senior Member

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.

Report message to a moderator

Re: How to improve the speed of mysql query using count(*) [message #1195 is a reply to message #1182 ]

Fri, 04 May 2007 01:52

julian
Messages: 13
Registered: March 2007

Junior Member

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

Report message to a moderator

Re: How to improve the speed of mysql query using count(*) [message #1196 is a reply to message #1134 ]

Fri, 04 May 2007 05:12

sterin
Messages: 323
Registered: March 2007
Location: Sweden

Senior Member

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

Report message to a moderator

Previous Topic:	Block user connections
Next Topic:	Lost connection to MySQL server during query

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 6 19:28:14 EST 2009

Total time taken to generate the page: 0.02171 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.