SQL Query too slow even "Using Index" on EXPLAIN

Question

i am trying to get all subscribers of a user

my query:

SELECT
    COUNT(sub.id) as ids
FROM
    subscribers as sub
WHERE
    suid=541839243781

EXPLAIN prints:

╔════╦═════════════╦═══════╦══════╦═══════════════╦═════╦═════════╦═══════╦═══════╦═════════════╗
║ id ║ select_type ║ table ║ type ║ possible_keys ║ key ║ key_len ║  ref  ║ rows  ║    Extra    ║
╠════╬═════════════╬═══════╬══════╬═══════════════╬═════╬═════════╬═══════╬═══════╬═════════════╣
║  1 ║ SIMPLE      ║ sub   ║ ref  ║ i3            ║ i3  ║       8 ║ const ║ 47890 ║ Using index ║
╚════╩═════════════╩═══════╩══════╩═══════════════╩═════╩═════════╩═══════╩═══════╩═════════════╝

so at the moment the total count i get is around 48k and it takes 0.0333 to load... what if this goes up to 1m or 5m ?? then it could take ages to load up...

my indexes on subscribers table are:

╔═════════════╦════════════╦═══════════════════╦══════════════╦═════════════╦═══════════╦═════════════╦══════════╦════════╦══════╦════════════╦═════════╗
║    Table    ║ Non_unique ║     Key_name      ║ Seq_in_index ║ Column_name ║ Collation ║ Cardinality ║ Sub_part ║ Packed ║ Null ║ Index_type ║ Comment ║
╠═════════════╬════════════╬═══════════════════╬══════════════╬═════════════╬═══════════╬═════════════╬══════════╬════════╬══════╬════════════╬═════════╣
║ subscribers ║          0 ║ PRIMARY           ║            1 ║ id          ║ A         ║       60251 ║ NULL     ║ NULL   ║      ║ BTREE      ║         ║
║ subscribers ║          1 ║ total_subscribers ║            1 ║ id          ║ A         ║       60251 ║ NULL     ║ NULL   ║      ║ BTREE      ║         ║
║ subscribers ║          1 ║ total_subscribers ║            2 ║ suid        ║ A         ║       60251 ║ NULL     ║ NULL   ║      ║ BTREE      ║         ║
║ subscribers ║          1 ║ i3                ║            1 ║ suid        ║ A         ║        6025 ║ NULL     ║ NULL   ║      ║ BTREE      ║         ║
║ subscribers ║          1 ║ i3                ║            2 ║ uid         ║ A         ║       60251 ║ NULL     ║ NULL   ║      ║ BTREE      ║         ║
║ subscribers ║          1 ║ i3                ║            3 ║ id          ║ A         ║       60251 ║ NULL     ║ NULL   ║      ║ BTREE      ║         ║
╚═════════════╩════════════╩═══════════════════╩══════════════╩═════════════╩═══════════╩═════════════╩══════════╩════════╩══════╩════════════╩═════════╝

so how can i make this query more efficient?

If I'm reading data correctly you don't have dedicated index for "suid" column. Is "suid" column values unique globally or only for each subscriber? — Vadim Ponomarev

Eric Petroelje Eric Petroelje · Accepted Answer · 2012-04-17T17:13:04

You probably can't.

That said, I would expect the COUNT operation to necessarily scale linearly with the number of rows. You may find that with 1 million rows it takes 0.12 seconds instead of 0.0333 seconds.

If and when it actually becomes a problem, you might be able to use pre-calculation and caching to solve this. For example you might have an hourly job that calculates the counts and stores them on an table. Your counts could be up to an hour out of date, but retrieving them will be much faster.

SQL Query too slow even "Using Index" on EXPLAIN

3 Answers