Return to Question

UPDATE 5 - MySQL 8.0

Iv'e exported the data to a 8.0 instance and both the fast-query and slow-query are working fast.

But this is not enough since combining two of them together (which is actually a business requirement) is slow, probably because mysql should choose a single index and each team size dictates a different optimal index but both does not have an optimal index but multiple (2).

The query that doesnt work on 8.0:

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 in (7,9)
        order by tbl.lastLoginDate asc

UPDATE 6 - SQL-Server experiment

Iv'e exported the tables into a sql server instance. all queries executes fast. explaining the query plan for the and tbl2.teamId in (7,9) ... shows that 3 different indexes are used - one for the mapping table, and two for the main customer table.

Is this the obvious use case where sql server is preferable?

UPDATE 4 - queries with paginations:

id , select_type,      table,     partitions,   type,   possible_keys,   key,   key_len,    ref,   rows,   filtered,  extra


// SLOW QUERY without force index (using limit 10K like we do in production, when team includes 7811365 players  execution time 80 seconds. returns 10K rows)

1,   simple,   tbl2,   null,   ref,    PRIMARY,  PRIMARY,   8,  const,   3448866,  100.00, Using index; Using temporary; Using filesort

1,   simple,   tbl,   null,   eq_ref,    PRIMARY,  PRIMARY,   8,  db.tbl2.customerId,   1,  100.00, "  " 


// FAST QUERY without force index (using limit 10K like we do in production, when team includes 10 players  execution time 0.1 seconds. returns 10 rows)

1,   simple,   tbl2,   null,   ref,    PRIMARY,  PRIMARY,   8,  const,   10,  100.00, Using index; Using temporary; Using filesort

1,   simple,   tbl,   null,   eq_ref,    PRIMARY,  PRIMARY,   8,  db.tbl2.customerId,   1,  100.00, "  " 


// SLOW QUERY  with force index (using limit 10K like we do in production, when team includes 7811365 players  execution time 0.1 seconds. returns 10K rows)

1,   simple,   tbl,   null,   index,    null,  lastLoginIndex,   6,  null,   7587368,  100.00, null

1,   simple,   tbl2,   null,   eq_ref,    PRIMARY,  PRIMARY,   16,  db.tbl2.customerId,   1,  100.00, Using Index

id , select_type,      table,     partitions,   type,   possible_keys,   key,   key_len,    ref,   rows,   filtered,  extra


// SLOW QUERY  (when team includes 7811365 players,  execution time 80 seconds. returns 7811365 rows)

1,   simple,   tbl2,   null,   ref,    PRIMARY,  PRIMARY,   8,  const,   3448866,  100.00, Using index; Using temporary; Using filesort

1,   simple,   tbl,   null,   eq_ref,    PRIMARY,  PRIMARY,   8,  db.tbl2.customerId,   1,  100.00, "  " 


// FAST QUERY  (when team includes 10 players,  execution time 0.1 seconds. returns 10 rows)

1,   simple,   tbl2,   null,   ref,    PRIMARY,  PRIMARY,   8,  const,   10,  100.00, Using index; Using temporary; Using filesort

1,   simple,   tbl,   null,   eq_ref,    PRIMARY,  PRIMARY,   8,  db.tbl2.customerId,   1,  100.00, "  " 

id , select_type,      table,     partitions,   type,   possible_keys,   key,   key_len,    ref,   rows,   filtered,  extra


// SLOW QUERY  (when team includes 7811365 players,  execution time 80 seconds. returns 7811365 rows)

1,   simple,   tbl2,   null,   ref,    PRIMARY,  PRIMARY,   8,  const,   3448866,  100.00, Using index; Using temporary; Using filesort

1,   simple,   tbl,   null,   eq_ref,    PRIMARY,  PRIMARY,   8,  db.tbl2.customerId,   1,  100.00, "  " 


// FAST QUERY  (when team includes 10 players,  execution time 0.1 seconds. returns 10 rows)

1,   simple,   tbl2,   null,   ref,    PRIMARY,  PRIMARY,   8,  const,   10,  100.00, Using index; Using temporary; Using filesort

1,   simple,   tbl,   null,   eq_ref,    PRIMARY,  PRIMARY,   8,  db.tbl2.customerId,   1,  100.00, "  " 


// SLOW QUERY  with force index (when team includes 7811365 players,  . returns 7811365 rows)

1,   simple,   tbl,   null,   index,    null,  lastLoginIndex,   6,  null,   7587368,  100.00, null

1,   simple,   tbl2,   null,   eq_ref,    PRIMARY,  PRIMARY,   16,  db.tbl2.customerId,   1,  100.00, Using Index