We have a single table that has around 20 - 30 columns describing our customers details (id, name, loginName, dateOfBirth, lastLoginDate, country ...) table size is around 15 million rows.

We have many ways on our Backoffice to query \ filter \ sort this table and it works perfectly fast since this table is heavily indexed. (more than 20 indexes).

a new feature request was introduced last week that actually broke the performance in a devastating way:

the requirement was to add teams to our Backoffice. and assign this teams to our customers. (basically team #1 can be mapped to customers 1-100,000 and team #2 to customers 100,001 - 200,000 etc..)

To accommodate the teams feature we had to change the way we query the customers table since now we need to add the logic : ".. And the customer is mapped to MY_TEAM"

This is very easy to do if a customer can be mapped into a single team - we simply extend the above table with a new column called teamId and that it.

Problem is that customers might have more than one team (many to many relation) and this is where normalizing these mapping is harder in a single table.

We tried to add to the query this:

...  AND customerTable.customerId IN
        ((SELECT t2.customerId
        FROM TeamCustomerMapping t2  WHERE t2.teamId = 7)  

This works in its default form but when the filter \ sorts are applied to the customer table this is where it takes forever to run (I believe its because MySQL must use one index and without sorts and filters it uses the primary customerId index which helps it with the join but when you want to sort it should use a different index but then the join is impacted).

Any ideas how to handle this?

UPDATE - the actual query

SELECT
    tbl.customerId,
    tbl.country,
    tbl.language,
    tbl.lastLoginDate
FROM    
   CustomerTable tbl  
   INNER JOIN TeamCustomerMapping tbl2 on tbl.customerId= tbl2.customerId
WHERE
    1 = 1                       
        and tbl2.teamId = 9
        order by tbl.lastLoginDate asc

when teamId = 9 the query is slow, when teamId = 7 its fast. the difference is how big teamId = 7 vs 9 is (one includes millions of customers the other only 10 customers)

adding the force index (lastLoginDate index) works fast only when teamId = 7 and slow when teamId = 9.