MySQL count and group by query slow even with indexing-CodePudding

I have a simple query

SELECT userId, COUNT(*) AS c 
FROM my_table 
WHERE someId = 11 AND userId != 0 
GROUP BY userId 
ORDER BY c DESC 
LIMIT 0, 5;

I have index on userId and someId, However, in each case, the query took 2,5s on table with 1M rows.

Explain output gives me:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra 
1   SIMPLE         my_table  ref    userId,someId  someId     4        const    6895    Using where; Using temporary; Using filesort

I have also tried to create two-column index on (someId, userId) or (userId, someId) but speed was also the same.

Result of SHOW CREATE TABLE my_table with removed additional unused columns

CREATE TABLE `my_table` (
  `userId` int(11) NOT NULL DEFAULT 0,
  `someId` int(11) NOT NULL,  
  #some other unused colums
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `someId` (`someId`),
  KEY `userId` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=746173

Result of SHOW TABLE STATUS WHERE name LIKE 'my_table

Name    Engine  Version     Row_format  Rows    Avg_row_length  Data_length     Max_data_length     Index_length    Data_free   Auto_increment  Create_time     Update_time     Check_time  Collation   Checksum    Create_options  Comment     Max_index_length    Temporary   
my_table    InnoDB  10  Dynamic     728295  162     118079488   0   125009920   19922944    746173  2022-08-22 19:47:41     NULL    NULL    utf8_general_ci     NULL            0   N

CodePudding user response：

Suggestions to consider,

ALTER TABLE my_table ADD INDEX my_table_ndx_someid_userid (someid, userid);

for a covering index.

Change in the query, WHERE someid = 11 AND userid != 0
to WHERE someid = 11 AND userid > 0
for positive testing of value.

What is the new timing?