0

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.

The TeamCustomerMapping Table has the following Primary index: (teamId,customerId)

UPDATE #2: Show create tables:

CREATE TABLE `TeamCustomerMapping ` (
  `customerId` bigint(20) NOT NULL,
  `teamId` bigint(16) NOT NULL,
  PRIMARY KEY (`teamId`,`customerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `CustomerTable` (
  `customerId` bigint(20) NOT NULL,
  `timeCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `timeUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `country` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `language` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastLoginDate` datetime DEFAULT NULL
  PRIMARY KEY (`customerId`),
  KEY `countryIndex` (`country`),
  KEY `lastLoginIndex` (`lastLoginDate`),
  KEY `languageIndex` (`language`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

UPDATE #3 adding explain of the queries:

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


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


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?

4
  • The customer can belong to 0,1,or more groups at once. as for the join. we tried it too - and its still slow when we try to sort
    – Urbanleg
    Nov 17, 2020 at 10:30
  • The customer can belong to 0,1,or more groups at once I see. And I see that there exists a junction table. I'm interested in the outer criteria for searching.
    – Akina
    Nov 17, 2020 at 10:31
  • and its still slow when we try to sort This is the expected behavior - I cannot imagine the index which will improve the sorting too..
    – Akina
    Nov 17, 2020 at 10:35
  • One query returns 10 rows, one returns 7811365 rows. Why are you comparing there speeds? They are necessarily not equivalent.
    – Rick James
    Dec 2, 2020 at 16:32

3 Answers 3

1

The problem is that for the large team it may be more efficient to start read index on CustomerTable and then check records in TeamCustomerMapping. As you see it works when you use limit for the amount of rows. It may be a bit tricky for MySQL server to estimate the amount of rows which it needs to read before it will find 10000 of necessary ones. But you can try to help it with hint. For example, you can run "select count(1) from TeamCustomerMapping where teamId = 9" first and if it's greater than 100000 or 300000 then run query with corresponding hint. Things may become more complicated when you add more filters. What MySQL server version do you use?

4
  • I was trying to create a one query to rule them all if able. i provided only a subset of the columns of the CustomerTable. we basically have dozens of them and dozens of different filters so yea, it might be complex. We use MySQL 5.7
    – Urbanleg
    Nov 18, 2020 at 12:40
  • Re structuring our tables is also a possibility. can you think of a better way to organize our tables?
    – Urbanleg
    Nov 18, 2020 at 12:44
  • 1
    @Urbanleg Not sure about different schema. You schema looks ok but data distribution is too different for different teams. I would test it with newer version of MySQL or maybe MariaDB server (if they will make different estimations and choose different plans for different parameter values, in my experience MS SQL, for example, makes better work in similar cases but it costs money). If it doesn't help I would try to run queries with different hints (or using straight_join) for different team sizes and may be sets of parameters. Nov 18, 2020 at 15:54
  • Thanks for your help, Tomorrow morning i will check this on a mysql version 8.0.
    – Urbanleg
    Nov 18, 2020 at 16:56
0
FROM    
   CustomerTable tbl  
   INNER JOIN TeamCustomerMapping tbl2 on tbl.customerId= tbl2.customerId
WHERE
    1 = 1                       
        and tbl2.teamId = 9
        order by tbl.lastLoginDate asc

For at least this one query, you need this composite index (possibly the PRIMARY KEY): INDEX(teamId, customerId)

Is customerId the PRIMARY KEY of tbl? If not, then you need INDEX(customerId, lastLoginDate)

Are you leaving out something? Or does the query really return "millions" of rows?

For other queries, you may need the mapping table to work the other direction; add INDEX(customerId, teamId).

3
  • @Urbanleg - Let's see some common examples of what occurs in place of 1=1.
    – Rick James
    Nov 17, 2020 at 23:12
  • can we handle the original case i posted?
    – Urbanleg
    Nov 18, 2020 at 7:23
  • teamid=7 vs teamid IN (7,9) can make a big difference. With/without LIMIT can make a big difference. That's 4 different queries, with (potentially) 4 different elapsed times. And possibly multiple indexing and/or query formulation strategies. I'm lost on all the Updates; what would you like to focus on?
    – Rick James
    Dec 2, 2020 at 16:39
0

We finally managed to make it work even on 5.7, we re write the query to:

SELECT
    tbl.customerId,
    tbl.country,
    tbl.language,
    tbl.lastLoginDate
FROM    
   CustomerTable tbl  

WHERE
    1 = 1                       
     AND tbl.customerId IN (SELECT customerId from TeamCustomerMapping where teamId = 7 UNION SELECT customerId from TeamCustomerMapping where teamId = 9)
        order by tbl.lastLoginDate asc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.