MySQL Slow query for 'COUNT'

Question

The following query takes 0.7s on a 2.5Ghz dual core Windows Server 2008 R2 Enterprise when run over a 4.5Gb MySql database. sIndex10 is a varchar(1024) column type:

SELECT COUNT(*) FROM e_entity
WHERE meta_oid=336799 AND sIndex10 = ''

An EXPLAIN shows the following information:

id: 1
select_type: SIMPLE
table: e_entity
type: ref
possible_keys: App_Parent,sIndex10
key: App_Parent
key_len: 4
ref: const
rows: 270066
extra: Using Where

There are 230060 rows matching the first condition and 124216 rows matching the clause with AND operator. meta_oid is indexed, and although sIndex10 is also indexed I think it's correctly not picking up this index as a FORCE INDEX (sIndex10), takes longer. We have had a look at configuration parameters such as innodb_buffer_pool_size and they look correct as well.

Given that this table has already 642532 records, have we reached the top of the performance mysql can offer? Is it at this point investing in hardware the only way forward?

You have 'possible_keys: App_Parent,sIndex10' wht yu don't have meta_oid ? . have you proper index on this column? eventually show your table schema — ScaisEdge, Nov 21 '16 at 08:05
A small performance gain might be achieved if app_parent covered BOTH columns — Strawberry, Nov 21 '16 at 08:09

score 0 · Answer 1 · answered Nov 21 '16 at 19:25

0

WHERE meta_oid=336799 AND sIndex10 = ''

begs for a composite index

INDEX(meta_oid, sIndex10)  -- in either order

That is not the same as having separate indexes on the columns.

That's all there is to it.

Index Cookbook

answered Nov 21 '16 at 19:25

Rick James

135,179
13
127
222

How about the queries that use `meta_oid` without querying `sIndex10`? – Rafael Nov 22 '16 at 02:19
They can use that index, too (assuming the given order). Queries using only sIndex10 cannot. The Cookbook says "`WHERE aaa = 123 AND ...`: an INDEX _starting_ with `aaa` is good", etc. – Rick James Nov 22 '16 at 02:38
This didn't make any difference – Rafael Nov 22 '16 at 06:38
If most of the rows have `sIndex10 = ''`, then my suggested index won't help much. – Rick James Nov 22 '16 at 18:55
53% of the records matching `meta_oid` also have `sIndex10 = ''` – Rafael Nov 23 '16 at 05:08

score -1 · Answer 2 · answered Nov 21 '16 at 08:55

-1

One thing I alway do is just count(id) since id is (nearly) always indexed, counting just the id only has to look at the index.

So try running and see if it performs any better. You should also add SQL_NO_CACHE when testing to get a better idea of how the query performs.

SELECT SQL_NO_CACHE COUNT(id) FROM e_entity
WHERE meta_oid=336799 AND sIndex10 = ''

Note: This is probably not the complete answer for your question, but it was too long for just a comment.

answered Nov 21 '16 at 08:55

Shaun Parsons

362
1
11

Thanks but this made no difference – Rafael Nov 21 '16 at 09:16
`SQL_NO_CACHE` bypasses the Query cache, which, if ON, could give very fast timings for _repeated_ queries. – Rick James Nov 21 '16 at 19:27
`COUNT(x)` checks `x` for being `NULL`. That is _rarely_ what you want, so `COUNT(*)` is the _usual_ way to write it. No, the index is irrelevant because the `WHERE` clause is what needs the index. – Rick James Nov 21 '16 at 19:28

MySQL Slow query for 'COUNT'

2 Answers2