7

I'm working on a query where I need to use IN, BETWEEN, GROUP BY, JOIN, ORDER BY all in one query. I am struggling with performance for that query, so I need help to choose indexes or to make changes to table structures if indexes won't help.

Some considerations

  1. Number of rows for both below tables are in millions.
  2. There is functionality where user can filter list by name, age, gender etc.
  3. There is functionality where user can sort list by some metrics like age, visits_count etc.
  4. Need Pagination for list.

Table Structures

Table 1

CREATE TABLE `table_1` (
  `visitor_id` varchar(32) CHARACTER SET ascii NOT NULL,
  `name` varchar(200) NOT NULL,
  `gender` varchar(1) NOT NULL DEFAULT 'M',
  `mobile_number` int(10) unsigned DEFAULT NULL,
  `age` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `visits_count` mediumint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`visitor_id`),
  KEY `indx_t1_test` (`visitor_id`,`visits_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Table 2

CREATE TABLE `table_2` (
  `company_id` bigint(20) unsigned NOT NULL,
  `visitor_id` varchar(32) CHARACTER SET ascii NOT NULL,
  `time_duration` mediumint(5) unsigned NOT NULL DEFAULT '0',
  `visited_on` date NOT NULL,
  PRIMARY KEY (`company_id`,`visitor_id`,`visited_on`),
  KEY `indx_t2_test` (`visited_on`,`company_id`,`visitor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Most Basic Data I want to retreive

Want to get 20 (Pagination) unique visitors (GROUP BY / DISTINCT) that visited particular group of companies (IN part) between selected time (BETWEEN part) period order by their age (ORDER BY part).

Query 1

First query if I write down for this then it would be:

SELECT
    t1.visitor_id
FROM table_1 AS t1
INNER JOIN table_2 AS t2 ON t2.visitor_id = t1.visitor_id
WHERE
    t2.company_id IN (528,211,1275,521,1299,493,492,852,868,869,1235,486,485,1238,855,1237,651,538,1241,1240,548,543,1247,1253,490,468,582,583,569,477,488,802,1294,518,1274,476,545,1267,556,479,1266,1265,541,1189,1263,1152,1260,478,1257,885,1139,1256,804,708,547,561,1239,1142,1226,1148,1230,529,1223,1192,1191,874,830,822,818,817,794,718,487,709,706,705,669,513,455) AND
    t2.visited_on BETWEEN '2015-01-01' AND '2017-01-31'
GROUP BY t1.visitor_id
ORDER BY t1.`visits_count` DESC
LIMIT 20;

When I run this query for any single company, it returns data fast enough (when the amount of matching rows are in small number, query performance is good).

The issue is when number of companies increases in IN part of query (I need to support 100 companies for this part of query), it takes time about 36 seconds to return result.

Explain output of this query is:

enter image description here

enter image description here

Query 2

Second query I can think of for same case, then it would be something like this:

SELECT
(
    SELECT  
        t2.visitor_id
    FROM table_2 AS t2
    WHERE 
        t2.company_id IN (528,211,1275,521,1299,493,492,852,868,869,1235,486,485,1238,855,1237,651,538,1241,1240,548,543,1247,1253,490,468,582,583,569,477,488,802,1294,518,1274,476,545,1267,556,479,1266,1265,541,1189,1263,1152,1260,478,1257,885,1139,1256,804,708,547,561,1239,1142,1226,1148,1230,529,1223,1192,1191,874,830,822,818,817,794,718,487,709,706,705,669,513,455)
        AND t2.visitor_id = t1.`visitor_id`
        AND t2.visited_on BETWEEN '2015-01-01' AND '2017-01-31'
    LIMIT 1
) AS visitor_id
FROM `table_1` AS t1
HAVING visitor_id IS NOT NULL
ORDER BY t1.`visits_count` DESC
LIMIT 0, 20

Behaviour of this query is opposite to the first one. If I run query for company which have few visitors, performance of this query is very low (it took about 38 seconds) (only one company in IN part, and that company have only 3-4 visitors). When number of companies in IN part is high, it returns results faster compared to one company (it took approx 13 seconds), but still not usable performance.

Explain output of this query is:

enter image description here

Query 3

To eliminate use of IN part of query, I created temporary table and added company ids in that table and then used JOIN:

SELECT
    DISTINCT
    t1.visitor_id
FROM `table_1` AS t1
INNER JOIN `table_2` AS t2 ON t1.`visitor_id` = t2.visitor_id
INNER JOIN temp_table AS t3 ON t3.company_id = t2.company_id
ORDER BY t1.`visits_count` DESC
LIMIT 0, 20;

This query is also taking time up to 22 seconds. I need performance up to 2-3 seconds for this listing.

Additional information

  • innodb_buffer_pool_size is 12GB
  • RAM is 30 GB
  • I'm using AWS RDS db.r3.xlarge instance
  • SHOW TABLE STATUS output is as follows:
    enter image description here

    1. The query SELECT COUNT(*) FROM table_2 WHERE company_id IN (...) AND visited_on BETWEEN '2015-01-01' AND '2017-01-31' returns 2660123

    2. For the first time only it is taking time. If I run the same query again it is very much faster (0.2 seconds). But, if I change the limit to LIMIT 20, 20 then again it repeats 24 seconds for first time, and same query second time is faster. It may be because of innodb_buffer_pool_size.

    3. Output of EXPLAIN FORMAT=JSON SELECT ...; is as follows.

      {
      "query_block": {
      "select_id": 1,
      "ordering_operation": {
        "using_filesort": true,
        "grouping_operation": {
          "using_temporary_table": true,
          "using_filesort": false,
          "nested_loop": [
            {
              "table": {
                "table_name": "t2",
                "access_type": "range",
                "possible_keys": [
                  "PRIMARY",
                  "indx_t2_test"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "company_id"
                ],
                "key_length": "8",
                "rows": 17301,
                "filtered": 100,
                "using_index": true,
                "attached_condition": "((`db`.`t2`.`company_id` in (528,211,1275,521,1299,493,492,852,868,869,1235,486,485,1238,855,1237,651,538,1241,1240,548,543,1247,1253,490,468,582,583,569,477,488,802,1294,518,1274,476,545,1267,556,479,1266,1265,541,1189,1263,1152,1260,478,1257,885,1139,1256,804,708,547,561,1239,1142,1226,1148,1230,529,1223,1192,1191,874,830,822,818,817,794,718,487,709,706,705,669,513,455)) and (`db`.`t2`.`visited_on` between '2015-01-01' and '2017-01-31'))"
              }
            },
            {
              "table": {
                "table_name": "t1",
                "access_type": "eq_ref",
                "possible_keys": [
                  "PRIMARY",
                  "indx_t1_test"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "visitor_id"
                ],
                "key_length": "34",
                "ref": [
                  "db.t2.visitor_id"
                ],
                "rows": 1,
                "filtered": 100
              }
            }
          ]
        }
      }
      }
      }
      

The output of the query suggested by Rick James:

SELECT
    t2.visitor_id
FROM (
    SELECT
        DISTINCT visitor_id
    FROM table_2
    WHERE 
        company_id IN (528,211,1275,521,1299,493,492,852, 868,
                        869,1235,486,485,1238,855,1237,651,538,1241,1240, 548,
                        543,1247,1253,490,468,582,583,569,477,488,802,1294, 518,
                        1274,476,545,1267,556,479,1266,1265,541,1189,1263, 1152,
                        1260,478,1257,885,1139,1256,804,708,547,561,1239, 1142,
                        1226,1148,1230,529,1223,1192,1191,874,830,822,818, 817,
                        794,718,487,709,706,705,669,513,455)
        AND visited_on BETWEEN '2015-01-01' AND '2017-01-31'
) AS t2
INNER JOIN table_1 AS t1 ON t2.visitor_id = t1.visitor_id
ORDER BY t1.`visits_count` DESC
LIMIT 20;

Explain output of the query is as follows:

enter image description here

This query is taking 58 seconds

enter image description here

Explain output of the inner subquery is as follows

enter image description here

enter image description here


The query:

SELECT
    COUNT(DISTINCT company_id, visited_on, visitor_id), 
    COUNT(DISTINCT company_id, LEFT(visited_on, 7), visitor_id), 
    COUNT(*) 
FROM table_2;

returns:

  • COUNT(DISTINCT company_id, visited_on, visitor_id) = 7607938.
  • COUNT(DISTINCT company_id, LEFT(visited_on, 7), visitor_id) = 5194480
  • COUNT(*) = 7607938

Note this output is with latest data, so number of rows in count(*) might have increased.

  • There's no good general purpose index for your queries, and it will depend very much on your data, e.g. on the probability of a row in table 1 to be in your resultset. How many results do you get for e.g. query 1 (without the limit of course)? If this is an integral part of your business model, you might want to think about precalculated result. As a test: could you try to add an index (visits_count) on table 1 and (visitor_id, company, visited_on) on table 2, try query 1 without the (wrong) group by, use select distinct t1.visitor_id instead, and force the use of these indexes? – Solarflare Feb 21 '17 at 0:04
  • @PareshBalar - The way the schema is structured, 2660123 rows will have to be checked. You were lucky it took only 58 seconds. Your query is checking 25 months. Is this typical? That is, do you want monthly tallies? Or might it be some other span. (I am thinking about Summary Tables.) – Rick James Feb 24 '17 at 5:57
  • @RickJames unfortunately its visitors listing with infinite scroll feature, also user can change date range to filter list. initial date range will be of 3 to 6 months only BUT possibility is that user can change that date range to 2 years OR more. I'm open to change schema if there is possibility for performance boost. if i just need to show monthly visitors count or some kind of statistics, then i can think of summary tables BUT not sure how will i create summary table for this kind of listing features ? – Paresh Balar Feb 24 '17 at 8:41
  • Hmmm... A visitor rarely visits a company twice in a month. This implies that a Summary Table broken down my month is unlikely to help much. – Rick James Feb 26 '17 at 18:40
2

age int(3) unsigned - That allows you to store ages up to 4 billion and wastes 4 bytes. Change to TINYINT UNSIGNED (1 bytes).

Ascii for names? Limited to the US? Even so, disallowing some odd names.

I'm puzzled by t2's PRIMARY KEY. Since the PK is Unique, this disallows recording more than one visit to a company for a person. If the restriction is OK, add this (in case the Optimizer decides that the data range is the best filter):

INDEX(visited_on, conpany_id, visitor_id)

If my hunch is correct then change the PK and add an index:

PRIMARY KEY(`company_id`, `visitor_id`, visited_on),
INDEX(visited_on, conpany_id, visitor_id)

Then check out your various Queries.

  • Tried adding both indexes you suggested, still query optmizer uses PRIMARY Index and query took time about 22 seconds – Paresh Balar Feb 12 '17 at 8:14
0

One more try.

SELECT  visitor_id
    FROM (
        SELECT  DISTINCT visitor_id
            FROM  table_2
            WHERE  company_id IN (528,211,1275,521,1299,493,492,852, 868,
                        869,1235,486,485,1238,855,1237,651,538,1241,1240, 548,
                        543,1247,1253,490,468,582,583,569,477,488,802,1294, 518,
                        1274,476,545,1267,556,479,1266,1265,541,1189,1263, 1152,
                        1260,478,1257,885,1139,1256,804,708,547,561,1239, 1142,
                        1226,1148,1230,529,1223,1192,1191,874,830,822,818, 817,
                        794,718,487,709,706,705,669,513,455 
                          )
              AND  visited_on BETWEEN '2015-01-01' AND '2017-01-31' 
        ) AS t2
    INNER JOIN  table_1 AS t1  ON t2.visitor_id = t1.visitor_id
    ORDER BY  t1.`visits_count` DESC
    LIMIT  20;

My reason for picking this approach was that the JSON seemed to say that the JOIN was unnecessarily being done before the GROUP BY. (I changed that to DISTINCT because it is a more logical way to state it.)

I am guessing that there are lots of visitors_ids after filtering on company_id and visited_on, but then that list gets whittled down a lot. Doing the whittling down before the JOIN is why this might be faster.

More

Now based on some info on the range of queries to be run and the distribution of the data...

You have two categories of queries coming from the same UI:

  • Nice, civilized, queries (small companies, short date range) where the Optimizer has a chance of doing a good job. (Suggest INDEX(company_id) and INDEX(visited_on).)
  • Other queries where you allow the user to ask for something that is inherently slow. (The Optimizer will eschew any index, and simply scan the entire table. And this is the best it can do.)

Normally, at this point I would sing the praises of Summary Tables. But you have a requirement that prevents such -- DISTINCT. (I do have a blog on how to rollup such, but I don't want to get into that.

Another thought is to PARTITION BY RANGE(TO_DAYS(visited_on)) and have the PRIMARY KEY start with company_id. The 2-dimensional nature of your quey is mentioned as one of the few use cases for partitioning here and discussed further here. (Partition by months.)

Or, you could avoid the problem by re-thinking the UI. If you limit the user to no more than 3 months, then there is a chance of using an index starting with visited_on.

Another thing... VARCHAR(32) is bulky. Normalize it down to MEDIUMINT UNSIGNED; that will shrink the data, making for less I/O, and better caching, hence more speed.

But, I repeat, you have a tough problem.

  • What fraction of the table is covered by that date range? Is that a typical range? Or is it often a smaller range? Or wider? – Rick James Mar 1 '17 at 18:41
  • Total number of records in table_2 (without any condition) are 7607938, and number of records with only date range condition are 7167282, No this daterange is not that typical, i have consider high level of scenario with more then 70 companies and 2 years of date range, usually default date range would be 3 months or 6 months BUT user can change that to 2 years or more. Same applies to company as well. for normal case company count would be 30-40. But in max case it would be more then 100 or 200. – Paresh Balar Mar 2 '17 at 5:58
  • Also number of records after where applied really depends of company size as well like if company is some kind of public service company then number of visitors can be high for that company meaning even if date range will be small and companies will be less in IN part, number of records can be high in result set. – Paresh Balar Mar 2 '17 at 6:01
-1

Need to add Index on ( visitor_id,age,visited_on ) this will improve the performace of query1 mentioned.

The Group by and Order by create temporary table due to which the query slows.

  • 1
    age and visited_on are in different tables, i don't think there is a way to add index you suggested. – Paresh Balar Feb 13 '17 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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