MySQL apply LIMIT and OFFSET to a selected table, rather than to the result set

Question

I have 3 tables as below.

mysql> describe IDN_CONFIG_RESOURCE;
+---------------+--------------+------+-----+-------------------+-------+
| Field         | Type         | Null | Key | Default           | Extra |
+---------------+--------------+------+-----+-------------------+-------+
| ID            | varchar(255) | NO   | PRI | NULL              |       |
| TENANT_ID     | int(11)      | NO   |     | NULL              |       |
| NAME          | varchar(255) | NO   | MUL | NULL              |       |
| CREATED_TIME  | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
| LAST_MODIFIED | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
| HAS_FILE      | tinyint(1)   | NO   |     | NULL              |       |
| HAS_ATTRIBUTE | tinyint(1)   | NO   |     | NULL              |       |
| TYPE_ID       | varchar(255) | NO   | MUL | NULL              |       |
+---------------+--------------+------+-----+-------------------+-------+
8 rows in set (0.09 sec)

mysql> describe IDN_CONFIG_TYPE;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ID          | varchar(255)  | NO   | PRI | NULL    |       |
| NAME        | varchar(255)  | NO   | UNI | NULL    |       |
| DESCRIPTION | varchar(1023) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe IDN_CONFIG_ATTRIBUTE;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ID          | varchar(255)  | NO   | PRI | NULL    |       |
| RESOURCE_ID | varchar(255)  | NO   | MUL | NULL    |       |
| ATTR_KEY    | varchar(1023) | NO   |     | NULL    |       |
| ATTR_VALUE  | varchar(1023) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+

My REST API response is as below. It returns an array of resource objects. Each resource may contain multiple attributes(Eg. 'from', 'to' etc.) as key value pairs stored in the IDN_CONFIG_ATTRIBUTE table. And linked to a resource by RESOURCE_ID column.

{
   "resources":[
      {
         "id":"e0dd8199-d45f-49c8-a4ca-6b863d4bc157",
         "tenantDomain":"carbon.super",
         "tenantDomain":"smtp",
         "resourceType":"e-mail",
         "lastModified":"2019-01-24T10:15:51Z",
         "created":"2019-01-24T10:15:51Z",
         "files":[
            {
               "value":"/resource/file/489684c4-77ee-47e0-ad74-38ccb0762672",
               "id":"489684c4-77ee-47e0-ad74-38ccb0762672"
            }
         ],
         "attributes":[
            {
               "key":"from",
               "value":"hey",
               "id":"de983448-8700-4fde-8a4b-f0becdb7ebe1"
            },
            {
               "key":"to",
               "value":"3wso2.com",
               "id":"4b4c5c85-dbed-46cb-b576-e15de4acc76a"
            }
         ],
         "hasFile":true,
         "hasAttribute":true
      },
      {
         "id":"4f731dc0-1581-4f00-b904-3d42bebdc9b6",
         "tenantDomain":"carbon.super",
         "resourceName":"pop3",
         "resourceType":"e-mail",
         "lastModified":"2019-01-24T10:15:51Z",
         "created":"2019-01-24T10:15:51Z",
         "files":[

         ],
         "attributes":[
            {
               "key":"server",
               "value":"pop3.abc.com",
               "id":"6f7aa9ee-2bca-4813-8405-9a5ea8728e2f"
            },
            {
               "key":"to",
               "value":"wso2.com",
               "id":"7569948c-9aed-451c-af4c-64c943e85c91"
            }
         ],
         "hasFile":false,
         "hasAttribute":true
      }
   ]
}

Issue

I'm going to call the API and retrieve all the resources which matches the search condition as below.

filter=tenantDomain eq carbon.super&limit=10&offset=0&sortBy=tenantId&order=ASC

So the generated query in the backend will be like below.

SELECT
  R.ID,
  R.TENANT_ID,
  R.NAME,
  R.CREATED_TIME,
  R.LAST_MODIFIED,
  T.NAME AS RESOURCE_TYPE,
  T.DESCRIPTION AS DESCRIPTION,
  F.ID AS FILE_ID,
  A.ID AS ATTR_ID,
  A.ATTR_KEY AS ATTR_KEY,
  A.ATTR_VALUE AS ATTR_VALUE
FROM
  IDN_CONFIG_RESOURCE AS R
  INNER JOIN IDN_CONFIG_TYPE AS T ON R.TYPE_ID = T.ID
  LEFT JOIN IDN_CONFIG_ATTRIBUTE AS A ON (
    R.HAS_ATTRIBUTE = TRUE
    AND A.RESOURCE_ID = R.ID
  )
  LEFT JOIN IDN_CONFIG_FILE AS F ON (
    R.HAS_FILE = TRUE
    AND F.RESOURCE_ID = R.ID
  )
WHERE
R.TENANT_ID = ?
ORDER BY R.TENANT_ID ASC
LIMIT 10 OFFSET 0

As you can see from both the query and the API filter condition, I'm trying to implement pagination and sorting for this search. I'm expecting to impose the pagination in the RESOURCE level. So that my API response would contain 10 resources only. But this query doesn't have any understanding of the RESOURCE level. It just throttles out the result tuples. (Which may belong to 1-10 resources)

I don't see any way to implement this. Any suggestions? Or let me know if the database design is wrong at the first place? P.S. The where condition is a dynamically generated one. It may have conditions to search in either column of either table. Not much flexibility on that though

Thanks in advance.

Answer 1

Thanks for your inputs. I've decided to follow the below approach.

1. Create a new DB view aggregating all 3 tables.

CREATE VIEW CONFIG_VIEW AS
SELECT    
        R.ID,
            R.TENANT_ID,
            R.NAME,
            R.CREATED_TIME,
            R.LAST_MODIFIED,
            T.NAME AS RESOURCE_TYPE,
            T.DESCRIPTION AS DESCRIPTION,
            F.ID AS FILE_ID,
            A.ID AS ATTR_ID,
            A.ATTR_KEY AS ATTR_KEY,
            A.ATTR_VALUE AS ATTR_VALUE
FROM 
IDN_CONFIG_RESOURCE AS R 
INNER JOIN IDN_CONFIG_TYPE AS T ON R.TYPE_ID = T.ID
LEFT JOIN IDN_CONFIG_ATTRIBUTE AS A ON (
  R.HAS_ATTRIBUTE = TRUE
  AND A.RESOURCE_ID = R.ID
)
LEFT JOIN IDN_CONFIG_FILE AS F ON (
  R.HAS_FILE = TRUE
  AND F.RESOURCE_ID = R.ID
)

2. Use below query to search and obtain the matching resource IDs.

SELECT DISTINCT ID
FROM CONFIG_VIEW
WHERE R.TENANT_ID = ?
LIMIT 50 OFFSET 0;

3. Then, use below query to obtain all the rows of the retrieved RESOURCE IDs from the below query.

SELECT 
  ID,
  TENANT_ID,
  NAME,
  CREATED_TIME,
  LAST_MODIFIED,
  RESOURCE_TYPE,
  DESCRIPTION,
  FILE_ID,
  ATTR_ID,
  ATTR_KEY,
  ATTR_VALUE
FROM
  CONFIG_VIEW
WHERE
  ID IN (
    '0a80998d-b8de-49e6-ad71-20014745a42a', '29fb9c05-1733-4bf5-918f-933f829633c7'
 )

Cheers.