mysql not using index w/ group_concat
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.)