# Very poor performance with select count(\*)

**URL:** <https://forums.percona.com/t/very-poor-performance-with-select-count/1243>\
**Category:** Other MySQL® Questions\
**Created:** [August 21, 2009, 12:20pm UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243 "2009-08-21T12:20:48Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Neil](https://avatars.discourse-cdn.com/v4/letter/n/839c29/32.png) [@Neil](https://forums.percona.com/u/Neil)\
**Post date:** [August 21, 2009, 12:20pm UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/1 "2009-08-21T12:20:48Z")

</div>

Hello, this is my first post )

I’m developing a log analysis application and I’m having a performance problem that I was hoping that someome could help (thanks in advance)

I have table QUERY that has almost 3 million rows. I also have a table Location containing rows for most cities of the world. The QUERY table has a LID (location id) that matches a specific location).

Although the complete query is not this simple, I’ve discovered where the performance problem arises.

The query is as simple as this:

SELECT QUERY, COUNT(\*) AS FREQUENCY  
FROM QUERY  
where lid=2703  
GROUP BY QUERY ORDER BY FREQUENCY DESC LIMIT 10

When I run this simple query with other lid values it is quite fast, but I discovered that a very large percentage of the queries has lid=2703 (over 1.8 million in almost 3 million). When I run the query, the results take over 200sec to be processed.

This is the table:

CREATE TABLE QUERY (  
QID INT UNSIGNED NOT NULL, (query id)  
SID INT UNSIGNED NOT NULL, (session id)  
QUERY VARCHAR(255) NOT NULL,  
LID INT UNSIGNED, (location id)  
PRIMARY KEY(QID)  
)ENGINE = MYISAM;

SHOW INDEXES FROM QUERY

table |Non\_unique |key\_name |seq\_in\_index |Column\_name |Collation |Cardinality |Sub\_part |Packed |Null |Index\_type

‘query’, 0, ‘PRIMARY’, 1, ‘QID’, ‘A’, 2387075, , ‘’, ‘’, ‘BTREE’, ‘’  
‘query’, 1, ‘QUERYSID\_NDX’, 1, ‘SID’, ‘A’, 596768, , ‘’, ‘’, ‘BTREE’, ‘’  
‘query’, 1, ‘QUERYLID\_NDX’, 1, ‘LID’, ‘A’, 132615, , ‘’, ‘YES’, ‘BTREE’, ‘’  
‘query’, 1, ‘QUERYQ\_NDX’, 1, ‘QUERY’, ‘A’, 341010, , ‘’, ‘’, ‘BTREE’, ‘’  
‘query’, 1, ‘QUERY\_NDX\_FULL’, 1, ‘QUERY’, ‘’, 140416, , ‘’, ‘’, ‘FULLTEXT’, ‘’

(At first I had only the full index for column query, then I added the “normal” B-tree index)

-----EXPLAIN

1, ‘SIMPLE’, ‘QUERY’, ‘index’, ‘QUERYLID\_NDX’, ‘QUERYQ\_NDX’, ‘767’, ‘’, 89, ‘Using where; Using temporary; Using filesort’

How can I display the top 10 queries for some location OR many locations, with good performance?

Could you please help?  
I know the word “urgent” is very used by this is the case…  
Thx

---

<div class="post-metadata">

**Author:** ![c113345](https://avatars.discourse-cdn.com/v4/letter/c/65b543/32.png) [@c113345](https://forums.percona.com/u/c113345)\
**Post date:** [August 21, 2009, 12:35pm UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/2 "2009-08-21T12:35:50Z")

</div>

have you tried using a multiple-column index on lid,query?

try:

alter table frequency add index(lid,query);

then:

select query,count(query) as frequency  
from query  
where lid=2703 group by query order by frequency desc limit 10

---

<div class="post-metadata">

**Author:** ![Neil](https://avatars.discourse-cdn.com/v4/letter/n/839c29/32.png) [@Neil](https://forums.percona.com/u/Neil)\
**Post date:** [August 21, 2009, 1:37pm UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/3 "2009-08-21T13:37:01Z")

</div>

Thank you very much for both your quick response and proposed solution!

It actually improved the performance!

I’ve used the code you provided:

alter table query add index(lid,query);

NOTE: for understanding the EXPLAIN that follows, the name of the new multiple index was created with the dedault name LID.

SELECT QUERY, COUNT(\*) AS FREQUENCY  
FROM LOCATION  
JOIN QUERY ON QUERY.LID=LOCATION.LID  
WHERE city=‘Mountain View’  
GROUP BY QUERY ORDER BY FREQUENCY DESC LIMIT 10

takes 85.5sec

When I run the this query I realize, using Explain, that the optimizer doesn’t choose the new LID index.

–Explain  
1, ‘SIMPLE’, ‘LOCATION’, ‘ref’, ‘PRIMARY,LOCATION\_CITY\_NDX’, ‘LOCATION\_CITY\_NDX’, ‘153’, ‘const’, 1, ‘Using where; Using temporary; Using filesort’

1, ‘SIMPLE’, ‘QUERY’, ‘ref’, ‘QUERYLID\_NDX,LID’, ‘QUERYLID\_NDX’, ‘5’, ‘logsbnfull.LOCATION.LID’, 18, ‘Using where’

NOTE: The value referred in the previous post (2703) is from city Mountain View.

So I hinted the optimizer to choose the LID index

SELECT QUERY, COUNT(\*) AS FREQUENCY  
FROM LOCATION  
JOIN QUERY USE INDEX (LID) ON QUERY.LID=LOCATION.LID  
WHERE city=‘Mountain View’  
GROUP BY QUERY ORDER BY FREQUENCY DESC LIMIT 10

takes 50.28sec

– EXPLAIN  
1, ‘SIMPLE’, ‘LOCATION’, ‘ref’, ‘PRIMARY,LOCATION\_CITY\_NDX’, ‘LOCATION\_CITY\_NDX’, ‘153’, ‘const’, 1, ‘Using where; Using temporary; Using filesort’

1, ‘SIMPLE’, ‘QUERY’, ‘ref’, ‘LID’, ‘LID’, ‘5’, ‘logsbnfull.LOCATION.LID’, 18, ‘Using where; Using index’

Can I do anything more to improve performance (since queries like these are only a small part of a results page and summing many times like these will be slow).

Thanks again for your great reply! )

Nelson

---

<div class="post-metadata">

**Author:** ![c113345](https://avatars.discourse-cdn.com/v4/letter/c/65b543/32.png) [@c113345](https://forums.percona.com/u/c113345)\
**Post date:** [August 21, 2009, 2:05pm UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/4 "2009-08-21T14:05:30Z")

</div>

try doing a count(query) instead of a count(\*)

if your table doesn’t change often, have you considered using a query\_cache?

try:  
show variables like ‘query\_cache%’

if you’re feeling ambitious, maybe a redesign or additional table could be in order; if you only care about aggregation why not store the count as a column that gets incremented when you execute a query? This would give you instant aggregation results, and if this table was merely added to your current schema, it shouldn’t affect performance.

CREATE TABLE QUERY\_count (  
QUERY VARCHAR(255) NOT NULL primary key unique,  
query\_count int unsigned not null default ‘0’,  
LID INT UNSIGNED,  
index(query\_count),  
index(LID)  
)ENGINE = MYISAM;

insert ignore into query\_count (query) vlaues(‘select’);  
update QUERY\_count set query\_count = query\_count + 1 where query=‘select’;

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [August 22, 2009, 5:24am UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/5 "2009-08-22T05:24:05Z")

</div>

YOU REALLY LIKE CAPS LOCK )

> > try doing a count(query) instead of a count(\*)  
> > bollocks, since query is NOT NULL, it is exactly the same query.

Try this:

SELECT QUERY, COUNT(\*) AS FREQUENCY  
FROM QUERY  
WHERE QUERY.lid=(SELECT lid FROM LOCATION WHERE city=‘Mountain View’)  
GROUP BY QUERY ORDER BY FREQUENCY DESC LIMIT 10

And include HAVING COUNT(_) \> 1 if there are many COUNT(_)'s equal to 1 that are guaranteed to never be in the output.

Do you really need VARCHAR(255)? Shorter length will speed up your query.

---

<div class="post-metadata">

**Author:** ![c113345](https://avatars.discourse-cdn.com/v4/letter/c/65b543/32.png) [@c113345](https://forums.percona.com/u/c113345)\
**Post date:** [August 24, 2009, 10:19am UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/6 "2009-08-24T10:19:19Z")

</div>

gmouse \> why would a shorter varchar speed up the query? I always thought that the length was just a semantic limit on length?

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [August 24, 2009, 1:30pm UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/7 "2009-08-24T13:30:48Z")

</div>

| [B]c113345@tyldd.com wrote on Mon, 24 August 2009 17:49[/B] |
| gmouse \> why would a shorter varchar speed up the query? I always thought that the length was just a semantic limit on length? |

It will not speed up my query if it is properly indexed, but it will speed up yours. Google for Generosity Can Be Unwise

---

<div class="post-metadata">

**Author:** ![Neil](https://avatars.discourse-cdn.com/v4/letter/n/839c29/32.png) [@Neil](https://forums.percona.com/u/Neil)\
**Post date:** [August 25, 2009, 5:03pm UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/8 "2009-08-25T17:03:19Z")

</div>

Thank you for your help! I’m still having performance issues…

I have:  
-a table with user queries (QUERY)  
-a table with locations (LOCATIONS)  
-a table that relates locations and time with queries (LOC\_TIME\_QUERIES)

Among many other things, I need to create a frequency chart based on the three levels (query, time, location). For every point of the chart I’m using the following query to get the frequency:

SELECT COUNT(QUERY) AS FREQUENCY  
FROM LOC\_TIME\_QUERY  
JOIN LOCATION ON LOCATION.LID=LOC\_TIME\_QUERY.LID  
JOIN QUERY ON QUERY.QID=LOC\_TIME\_QUERY.QID  
WHERE MATCH (QUERY) AGAINST (‘music’ IN BOOLEAN MODE)  
AND TIME\>= ‘20081201000000’ AND TIME \< ’ 20081203000000’

It is slow. So I decided to check a lighter version considering only the query and the time:

SELECT COUNT(\*) AS FREQUENCY  
FROM LOC\_TIME\_QUERY  
JOIN QUERY ON QUERY.QID=LOC\_TIME\_QUERY.QID  
WHERE MATCH (QUERY) AGAINST (‘portugal’ IN BOOLEAN MODE)  
and time \>= ‘20081201000000’ and time \< ’ 20081203000000’

it takes several seconds (10sec or so) to retrieve the results.  
Considering that this is to be repeated for all the point of the chart…it takes quite a while.

NOTE: When separated, they are fast. That is, if I only ask for the query results that match it is fast…  
if I only ask for the time results it is fast…  
but not when joined!!!  
I know that the query part is performed first… but I see that afterwards the time is not using the index

—EXPLAIN

ID|SELECT TYPE|TABLE|TYPE|POSSIBLE\_KEYS|KEY|KEY LEN|REF|ROWS|EXTRA  
1, ‘SIMPLE’, ‘QUERY’, ‘fulltext’, ‘PRIMARY,QUERY\_NDX\_FULL’, ‘QUERY\_NDX\_FULL’, ‘0’, ‘’, 1, ‘Using where’

1, ‘SIMPLE’, ‘LOC\_TIME\_QUERY’, ‘ref’, ‘PRIMARY,LTQ\_TIME\_NDX,LTQ\_QID\_NDX,LTQ\_TIMELID\_NDX,LTQ\_QIDTIM E\_NDX’, ‘LTQ\_QID\_NDX’, ‘4’, ‘logsbnfull.QUERY.QID’, 1, ‘Using where’

* * *

CREATE TABLE LOC\_TIME\_QUERY (  
QID INT UNSIGNED NOT NULL,  
TIME DATETIME,  
LID INT UNSIGNED,  
PRIMARY KEY (QID, TIME)  
)ENGINE = MYISAM;

CREATE INDEX LTQ\_TIME\_NDX USING BTREE ON LOC\_TIME\_QUERY(TIME);  
CREATE INDEX LTQ\_LID\_NDX USING BTREE ON LOC\_TIME\_QUERY(LID);  
CREATE INDEX LTQ\_QID\_NDX USING BTREE ON LOC\_TIME\_QUERY(QID);

I’ve tried other combinations of indexes…

Can I have your hints about improving the performance when considering the relation of query, time and location?

Thx

Nelson

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [August 26, 2009, 4:46pm UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/9 "2009-08-26T16:46:18Z")

</div>

Try a multi-column index on (QID,TIME,LID) in the table LOC\_TIME\_QUERIES.

And make sure LOCATIONS.LID is indexed (it is probably a primary key, which is fine).

---

<div class="post-metadata">

**Author:** ![Neil](https://avatars.discourse-cdn.com/v4/letter/n/839c29/32.png) [@Neil](https://forums.percona.com/u/Neil)\
**Post date:** [August 27, 2009, 7:18am UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/10 "2009-08-27T07:18:08Z")

</div>

I’ve tried it but the optimizer chooses the index for the QID alone. Even in the case of the second query I presented (the one with only query and time) it doesn’t choose the primary key, which is (qid,time).

I’ve tried to hint the optimizer to use other keys but doesn’t get any better.

While running, I can’t find a way to take advantage of the time indexation…

Thanks

Nelson

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [August 27, 2009, 7:39am UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/11 "2009-08-27T07:39:26Z")

</div>

Full explain output?

And try IGNORE INDEX / FORCE INDEX.

---

<div class="post-metadata">

**Author:** ![Neil](https://avatars.discourse-cdn.com/v4/letter/n/839c29/32.png) [@Neil](https://forums.percona.com/u/Neil)\
**Post date:** [August 27, 2009, 8:19am UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/12 "2009-08-27T08:19:29Z")

</div>

As I stated earlier, to simplify, right now I will only consider query and time.

SELECT COUNT(\*) AS FREQUENCY  
FROM LOC\_TIME\_QUERY2  
JOIN QUERY ON QUERY.QID=LOC\_TIME\_QUERY2.QID  
WHERE MATCH (QUERY) AGAINST (‘portugal’ IN BOOLEAN MODE)  
and time \>= ‘20081202000000’ and time \< ’ 20081202210000’

----\> 1891  
4,8sec

ID|SELECT\_TYPE|TABLE|TYPE|POSSIBLE\_KEYS|KEY|KEY\_LEN|REF|ROWS |EXTRTA  
1, ‘SIMPLE’, ‘QUERY’, ‘fulltext’, ‘PRIMARY,QUERY\_NDX\_FULL’, ‘QUERY\_NDX\_FULL’, ‘0’, ‘’, 1, ‘Using where’  
1, ‘SIMPLE’, ‘LOC\_TIME\_QUERY2’, ‘ref’, ‘PRIMARY,LTQ2\_TIME\_NDX,LTQ2\_QID\_NDX,QIDTIMELID\_NDX’, ‘LTQ2\_QID\_NDX’, ‘4’, ‘logsbnfull.QUERY.QID’, 1, ‘Using where’

The primary key is (qid,time). But I also indexed the following:

SHOW INDEXES FROM LOC\_TIME\_QUERY2

TABLE|NON\_UNIQUE|KEY\_NAME|SEQ\_IN\_INDEX|COLUMN\_NAME|COLLATION |CARINALITY|SUB\_PART|PACKED  
‘loc\_time\_query2’, 0, ‘PRIMARY’, 1, ‘QID’, ‘A’, , , ‘’, ‘’, ‘BTREE’, ‘’  
‘loc\_time\_query2’, 0, ‘PRIMARY’, 2, ‘TIME’, ‘A’, 2387075, , ‘’, ‘’, ‘BTREE’, ‘’  
‘loc\_time\_query2’, 1, ‘LTQ2\_TIME\_NDX’, 1, ‘TIME’, ‘A’, 2387075, , ‘’, ‘’, ‘BTREE’, ‘’  
‘loc\_time\_query2’, 1, ‘LTQ2\_LID\_NDX’, 1, ‘LID’, ‘A’, 132615, , ‘’, ‘YES’, ‘BTREE’, ‘’  
‘loc\_time\_query2’, 1, ‘LTQ2\_QID\_NDX’, 1, ‘QID’, ‘A’, 2387075, , ‘’, ‘’, ‘BTREE’, ‘’  
‘loc\_time\_query2’, 1, ‘QIDTIMELID\_NDX’, 1, ‘QID’, ‘A’, 2387075, , ‘’, ‘’, ‘BTREE’, ‘’  
‘loc\_time\_query2’, 1, ‘QIDTIMELID\_NDX’, 2, ‘TIME’, ‘A’, 2387075, , ‘’, ‘’, ‘BTREE’, ‘’  
‘loc\_time\_query2’, 1, ‘QIDTIMELID\_NDX’, 3, ‘LID’, ‘A’, 2387075, , ‘’, ‘YES’, ‘BTREE’, ‘’

If I use

…  
FROM LOC\_TIME\_QUERY2 use index (primary)  
…

or if

…  
FROM LOC\_TIME\_QUERY2 force index (primary)  
…

I doesn’t get better…or significantly better. 4.5sec

Explain (with force index):

1, ‘SIMPLE’, ‘QUERY’, ‘fulltext’, ‘PRIMARY,QUERY\_NDX\_FULL’, ‘QUERY\_NDX\_FULL’, ‘0’, ‘’, 1, ‘Using where’  
1, ‘SIMPLE’, ‘LOC\_TIME\_QUERY2’, ‘ref’, ‘PRIMARY’, ‘PRIMARY’, ‘4’, ‘logsbnfull.QUERY.QID’, 23870, ‘Using where; Using index’

* * *

When I try:  
…  
FROM LOC\_TIME\_QUERY2 ignore index (LTQ2\_QID\_NDX)  
…

the index the optimizer chooses the (qid,time,lid) index…which looks appropriate, but it gets worse: (5.1s)

EXPLAIN (with ignore index):

1, ‘SIMPLE’, ‘QUERY’, ‘fulltext’, ‘PRIMARY,QUERY\_NDX\_FULL’, ‘QUERY\_NDX\_FULL’, ‘0’, ‘’, 1, ‘Using where’  
1, ‘SIMPLE’, ‘LOC\_TIME\_QUERY2’, ‘ref’, ‘PRIMARY,LTQ2\_TIME\_NDX,QIDTIMELID\_NDX’, ‘QIDTIMELID\_NDX’, ‘4’, ‘logsbnfull.QUERY.QID’, 1, ‘Using where; Using index’

Your atention to this problem of mine is very appreciated. Thanks.  
Nelson

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [August 27, 2009, 9:31am UTC](https://forums.percona.com/t/very-poor-performance-with-select-count/1243/13 "2009-08-27T09:31:59Z")

</div>

The (qid,time,lid) will probably be better for the full query. Your PK suffices for the light query. Check your full query after all optimization is done; if (qid,time,lid) turns out to be not so much faster (or even slower) than the PK-thing, just drop (qid,time,lid).

I doubt the join is your problem. How fast is this query?

SELECT COUNT(\*) AS FREQUENCY  
FROM LOC\_TIME\_QUERY2  
WHERE MATCH (QUERY) AGAINST (‘portugal’ IN BOOLEAN MODE)

And this one

SELECT QID  
FROM LOC\_TIME\_QUERY2  
WHERE MATCH (QUERY) AGAINST (‘portugal’ IN BOOLEAN MODE)