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.