mysql COUNT() num rows too slow

Question

i am trying to get the number of subscribers of a user from a table which has 22m rows.

my sql is the following:

SELECT
    COUNT(id)
FROM
    subscribers
WHERE
    suid=541839243781

which needs 12.6020 s to load

however the following same query(gets subscriptions of a user) needs only 0.0036 s to load(seems ok)

SELECT
    COUNT(uid)
FROM
    subscribers
WHERE
    uid=541839243781

my EXPLAIN:

    id  select_type     table            type   possible_keys    key    key_len     ref     rows                 Extra
     1     SIMPLE    dvx_subscribers    index        4            4       16        NULL    22041275    Using where; Using index

SHOW CREATE TABLE:

    CREATE TABLE `subscribers` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `sid` bigint(20) unsigned NOT NULL,
 `uid` bigint(20) unsigned NOT NULL,
 `suid` bigint(20) unsigned NOT NULL,
 `date` datetime NOT NULL,
 KEY `id` (`id`),
 KEY `2` (`uid`,`suid`),
 KEY `4` (`suid`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=23226599 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INDEXES:

Table   Non_unique  Key_name    Seq_in_index    Column_name     Collation   Cardinality     Sub_part    Packed  Null    Index_type  Comment
subscribers     1   id  1   id  A   NULL    NULL    NULL        BTREE   
subscribers     1   2   1   uid     A   449821  NULL    NULL        BTREE   
subscribers     1   2   2   suid    A   459193  NULL    NULL        BTREE   
subscribers     1   4   1   suid    A   6115    NULL    NULL        BTREE   
subscribers     1   4   2   id  A   22041275    NULL    NULL        BTREE

how can i index it or optimize it to load as fast as possible? cause 12secs is hell too much for this...

@DanielKamilKozar. Why `suid` alone? (Upvoted the answer where you omitted that...) — gdoron, Apr 19 '12 at 17:41
@ypercube with the updated indexes it takes up 12 secs! the time doesnt exceed 12 seconds... — stergosz, Apr 19 '12 at 18:42

Sergey Kalinichenko · Accepted Answer · 2012-04-19T17:46:53.420

6

The second query runs against an index (uid), so it completes nearly instantaneously. The first one, however, needs to scan the table, because there is no suitable index to use. Create an index on (suid, id) to fix this problem.

edited Apr 19 '12 at 17:46

answered Apr 19 '12 at 17:39

Sergey Kalinichenko

714,442
84
1,110
1,523

2

The index should be on `suid` first, *then* on `id`. – Apr 19 '12 at 17:43
the index seems to work on EXPLAIN but when i run it as query the SELECT takes 12 seconds... thats weird... – stergosz Apr 19 '12 at 18:01
@fxuser This is very strange... Could you update the question with the added index and the new plan? – Sergey Kalinichenko Apr 19 '12 at 18:14
@dasblinkenlight i have updated create table, indexes and explain.. what do you mean "new plan"? – stergosz Apr 19 '12 at 18:27

score 0 · Answer 2 · answered Apr 19 '12 at 18:33

0

Since id can not be NULL, you can rewrite as:

SELECT
    COUNT(*)
FROM
    subscribers
WHERE
    suid=541839243781

Except for very recent (or future) optimizers, it should be a bit faster than COUNT(id).

Whole 4 seconds though, sounds too slow, even for MyISAM. Perhaps the index is fragmentated.

answered Apr 19 '12 at 18:33

ypercubeᵀᴹ

113,259
19
174
235

still the same time... what do you mean fragmentated?how can EXPLAIN be very fast and SELECT take 12 secs? – stergosz Apr 19 '12 at 18:42
`EXPLAIN` shows the query plan. It doesn't search the actual tables. – ypercubeᵀᴹ Apr 19 '12 at 18:45
it also says that INDEXES are used, isnt this correct?why it still takes so much time?this is very weird... – stergosz Apr 19 '12 at 18:46
The table has 23M rows. Did it have more and you deleted some (many) rows? – ypercubeᵀᴹ Apr 19 '12 at 18:50
Can you afford to run `ANALYZE TABLE subscribers` or [`OPTIMIZE TABLE subscribers`](http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html) ? It may take time and all users will be locked from the table. – ypercubeᵀᴹ Apr 19 '12 at 18:55
from analyze i got "Table is already up to date" and from optimize i didnt get anything unusual... also the time still remains the same... is 22m to much for mysql to proceed and get the rows?what else could delay the query? – stergosz Apr 19 '12 at 19:00
Can you run `# myisamchk -dvv subscribers.MYI` from the command line and post the output? – ypercubeᵀᴹ Apr 19 '12 at 19:02
Then it could be a configuration problem. Check your settings. [You can try this tool from Percona](http://www.mysqlperformanceblog.com/2011/12/23/online-mysql-configuration-wizard-from-percona/). – ypercubeᵀᴹ Apr 19 '12 at 19:07
i did run the command but i dont know what happened? - didnt get any output – stergosz Apr 19 '12 at 19:12

mysql COUNT() num rows too slow

2 Answers2

Linked