mysql not using index w/ group_concat

Question

I've got a simple temp table that I'm creating below

CREATE  TEMPORARY TABLE IF NOT EXISTS Helper0910912001524518044820 (
    usergroupID INT,
    StoreID INT ,
    INDEX st (`usergroupID`) 
    ) engine=memory AS 
SELECT  usergroup0.*
    from  
    (
        SELECT  156825 as usergroupID,
                MVStoreID as StoreID,
                1 as assignmentCount
            FROM  MVMasterEntity_AttachedStores 
    ) usergroup0 ;

/* queryTime - 0.5284 seconds */

I then query against it with the following query....

Select usergroupID, group_concat(StoreID) as Inspects
    from Helper0910912001524518044820
    group by usergroupID;

but am not having any luck with getting this query to use indexes. I've tried the following indexes.

( StoreID ) , ( StoreID, UsergroupID ), ( UsergroupID ), ( UsergroupID, StoreID )

The explain that I get is always the same.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  AssHelper0910912001524518044820 ALL                 903452  Using filesort

Any help explaining why I can't use indexes for this query would be greatly appreciated.

Answer 1

No index is useful. There is only one value of usergroupID (as you have written the query), so there is no use for it to be indexed.

So, the Optimizer decides to simply read the data without using any index.

One thing you can do is to get rid of the extra layer of SELECTs.

Another thing is to get rid of assignmentCount, since it is not used.

To achieve the same result set, simply do (without a tmp table):

SELECT  156825 AS usergroupID,
        GROUP_CONCAT(MVStoreID) as Inspects
    FROM  MVMasterEntity_AttachedStores 

With this formulation, INDEX(MVStoreID) would probably help.

(I suspect you watered down the actual code too much, and I have not answered your real question.)