MySQL : Select query performance issue

Question

We are facing performance related problem with select query. We have reports table which contains approximate 2 Crore (20 million) records.

When we are executing simple count(*) from to check count, its taking more than 1 minute to display result.

Here is the info about mysql, server and query

System info

  OS             : Debian 6.0.7
  Model          : AMD Opteron(tm) Processor 6172
  cpu MHz        : 2100.154
  cache size     : 512 KB
  processor      : 2

Memory       total       used       free     shared    buffers     cached
Mem:         16083       6335       9747          0        153       5323

Mysql info

mysql  Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64) using readline 6.1

my.conf settings

 key_buffer              = 16M
  max_allowed_packet      = 16M
  thread_stack            = 192K
  thread_cache_size       = 8
  max_connections        = 1000
  table_cache            = 128
  innodb_buffer_pool_size = 3G
  query_cache_limit       = 512M
  query_cache_size        = 3G

mysql> select count(*) from reports;
+-----------+
|  count(*) |
+-----------+
|  23311587 |
+-----------+
1 row in set (67.07 sec)

DB engine : Innodb.

EDIT : Query execution with index and without index

mysql> select count(id) from Reports USE INDEX(PRIMARY);

+-----------+
| count(id) |
+-----------+
|  17835433 |
+-----------+
1 row in set (55.56 sec)

mysql> 
mysql> select count(id) from Reports;

+-----------+
| count(id) |
+-----------+
|  17835433 |
+-----------+
1 row in set (55.65 sec)

I am struggling with performance issue, can anyone please help me to improve performance of table?

try just doing a count on an index/key isntead of * see if that makes a difference — Dave, Jan 27 '14 at 12:47
This could help: http://stackoverflow.com/questions/1332624/speeding-up-row-counting-in-mysql — NewInTheBusiness, Jan 27 '14 at 12:48
@GordonLinoff please see my edit have executed query with index and without index — Juned, Jan 27 '14 at 14:10

score 1 · Answer 1 · answered Jan 27 '14 at 12:47

1

Have a look at This

Try like

SELECT COUNT(coupon_id) FROM coupon USE INDEX (PRIMARY);

Where coupon_id is Primary Key in coupon table

answered Jan 27 '14 at 12:47

Abdul Manaf

4,768
3
27
34

score 1 · Answer 2 · answered Jan 27 '14 at 12:51

This is because you are using InnoDB.

InnoDB tables are slow on a simple count(*) query, because it needs to do a full table scan for that.

You could potentially increase performance of this query, by making it use the PRIMARY index.

select count(reportId) from reports USE INDEX(PRIMARY);

The real question here is: do you need to perform this kind of query a lot? Mostly you would do counts with a WHERE clause in it, which, given the correct indexes, should run just fine

score 1 · Answer 3 · answered Jan 27 '14 at 12:58

1

you can use index on the required column.

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

answered Jan 27 '14 at 12:58

Afnan Ahmad

2,492
4
24
44

score 0 · Answer 4 · answered Jan 27 '14 at 14:43

MySQL executes the count(*) by actually looking at the data. If you have a primary key index, it is going to scan the primary key index to get the results, rather than the original data. I am impressed that your system performance is so consistent that the same operation took 55.56 seconds in one case and 55.65 seconds in the other -- less than half a percent difference.

Doing the scan requires loading the index into memory. If the index doesn't fit into memory, then it will take longer. Check your system memory configuration to be sure that you can fit the 17 million records in at one time. Alas, I'm not intimately familiar with all the parameters for configuring MySQL, but on a machine with 16 Gbytes of memory and a buffer pool of 3Gbytes, there should be enough memory.

You may need to consider other options, if performance on this type of query is important. Ad hoc queries on a table with 17 million rows is going to take time. If you need speed performance, consider a data mart approach, where you extract and summarize data in ways that solve most users' problems.

Thanks for the answer, can you please provide me any relevant link for data mart, how do i implement it with MySQL — Juned, Jan 28 '14 at 05:35
A good book on the subject is Ralph Kimball's "The Data Warehouse Tool Kit". — Gordon Linoff, Jan 28 '14 at 14:35

MySQL : Select query performance issue

4 Answers4