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
– Vadim Ponomarev, Commented Apr 17, 2012 at 17:27

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

1

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.

answered Apr 17, 2012 at 17:13

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Larry Lustig · Accepted Answer · 2012-04-17 17:42:37Z

1

Does the column id allow NULL values? If not, change to SELECT COUNT(*) and the engine will be able to answer the query from the index alone without reference to the table data. This should speed things up and, depending on how MySQL stores and retrieves cardinality statistics, could make the query instantaneous.

answered Apr 17, 2012 at 17:42

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

4 Comments

stergosz Over a year ago

id is an AUTO_INCREMENT column but it doesnt seem to speed up things... i still get the same speed

Larry Lustig Over a year ago

You'll need a large data set to test in order to see how the query time changes with total count. Try creating 5 million rows and testing against that.

stergosz Over a year ago

on 1.2m rows it takes 0.2 secs so i guess on 5m it will take around 1-1.5 secs... cant this be scaled down to like 0.002 or something like that?? how other sites get such numbers from db so fast?do they store them somewhere and increase/decrease their count instead whenever a new record is created/deleted?

Larry Lustig Over a year ago

If you have an index on suid and are doing COUNT(*) or COUNT(suid) then you have given MySQL as much juice to answer the question quickly as you can. For more speed you will have to maintain a pre-computed table of suids and their matching counts.

Triple Gilaman · Accepted Answer · 2012-04-17 17:26:21Z

0

You can join sys.tables to sys.partitions. Row stats are stored there for the table.

Err: this applies to MS SQL Server, sorry should have mentioned that.

answered Apr 17, 2012 at 17:26

Triple Gilaman

4833 silver badges10 bronze badges

Collectives™ on Stack Overflow

SQL Query too slow even "Using Index" on EXPLAIN

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related