CODEWITHSUNDEEP
mysqlindexing
stergosz
stergosz

Reputation: 5860

mysql COUNT() num rows too slow

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...

Upvotes: 4

Views: 2361

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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.

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726579

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.

Upvotes: 6

Related Questions