CODEWITHSUNDEEP
mysqlsqlperformance
stergosz
stergosz

Reputation: 5860

SQL Query too slow even "Using Index" on EXPLAIN

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?

Upvotes: 0

Views: 418

Answers (3)

Larry Lustig
Larry Lustig

Reputation: 50970

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.

Upvotes: 1

Triple Gilaman
Triple Gilaman

Reputation: 483

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.

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60498

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.

Upvotes: 1

Related Questions