MariaDB Subquery – Resolving Join Issues

mariadbormsubquery

I'm trying hard as I can to transform my query to get rid of subqueries. I know its better to not use subqueries, but my first reason to change this query is because my ORM (Doctrine) isn't able to use any join with subquery, it doesn't support it (or CTEs).

Is there a way to get rid of subqueries in this query?

SELECT 
    s.id,
    e.exception,
    s.name,
    w.url,
    w.web_id,
    w.active,
    w.suspended,
    r.email,
    p.name AS partner,
    p.id AS partnerId,
    contacts.names AS contactNames,
    contacts.tels AS contactTels,
    contacts.emails AS contactEmails
FROM
    service s
        JOIN
    web w ON s.web_id = w.id
        JOIN
    rus r ON w.rus_id = r.id
        JOIN
    partner p ON r.partner_id = p.id
        LEFT JOIN
    exception e ON e.service_id = s.id
        LEFT JOIN
    (SELECT 
        p.id,
            GROUP_CONCAT(c.name) names,
            GROUP_CONCAT(c.tel) tels,
            GROUP_CONCAT(c.email) emails
    FROM
        partner p
    LEFT JOIN contact c ON c.partner_id = p.id
    WHERE
        c.main = 1 OR c.important = 1
    GROUP BY p.id) contacts ON contacts.id = p.id
        LEFT JOIN
    contact c ON c.partner_id = p.id

Tables and sample data:

CREATE TABLE `partner` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `ico` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `partner_idx_active` (`active`),
  FULLTEXT KEY `partnerEntity` (`name`,`ico`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `partner` (`id`, `name`, `ico`, `created`, `active`) VALUES
(1, 'partner1', '123',  '2021-05-18 22:27:24',  1);

CREATE TABLE `contact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `partner_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `tel` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  `main` int(11) DEFAULT NULL,
  `important` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_4C62E6389393F8FE` (`partner_id`),
  FULLTEXT KEY `contactEntity` (`name`,`email`,`tel`),
  CONSTRAINT `FK_4C62E6389393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `contact` (`id`, `partner_id`, `name`, `email`, `tel`, `created`, `active`, `main`, `important`) VALUES
(1, 1,  'contact1', 'test@test.com',    '123456789',    '2021-05-18 22:28:30',  1,  1,  NULL),
(2, 1,  'contact2', 'test2@test.com',   '123456788',    '2021-05-18 22:28:48',  1,  NULL,   1),
(3, 1,  'contact3', 'test3@test.com',   '123451234',    '2021-05-18 22:29:13',  1,  NULL,   NULL);


CREATE TABLE `rus` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `partner_id` int(11) DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_3370C8199393F8FE` (`partner_id`),
  KEY `rus_idx_active` (`active`),
  FULLTEXT KEY `rusEntity` (`email`),
  CONSTRAINT `FK_3370C8199393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `rus` (`id`, `partner_id`, `email`, `created`, `active`) VALUES
(1, 1,  'test@test.com',    '2021-05-18 22:27:36',  1);

CREATE TABLE `service` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `web_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_E19D9AD2FE18474D` (`web_id`),
  KEY `service_idx_active` (`active`),
  FULLTEXT KEY `serviceEntity` (`name`),
  CONSTRAINT `FK_E19D9AD2FE18474D` FOREIGN KEY (`web_id`) REFERENCES `web` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `service` (`id`, `web_id`, `name`, `created`, `active`) VALUES
(1, 1,  'service1', '2021-05-18 22:28:08',  1);

CREATE TABLE `exception` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `partner_id` int(11) DEFAULT NULL,
  `exception` longtext COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime DEFAULT NULL,
  `service_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_7FC98E6D9393F8FE` (`partner_id`),
  KEY `FK_7FC98E6DED5CA9E6` (`service_id`),
  CONSTRAINT `FK_7FC98E6D9393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`),
  CONSTRAINT `FK_7FC98E6DED5CA9E6` FOREIGN KEY (`service_id`) REFERENCES `service` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `exception` (`id`, `partner_id`, `exception`, `created`, `service_id`) VALUES
(1, 1,  'test..',   '2021-05-18 22:31:14',  1);

CREATE TABLE `web` (
  `suspended` int(11) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rus_id` int(11) DEFAULT NULL,
  `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `web_id` int(5) unsigned zerofill DEFAULT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_15C9385126907186` (`rus_id`),
  KEY `web_idx_active` (`active`),
  FULLTEXT KEY `webEntity` (`url`),
  CONSTRAINT `FK_15C9385126907186` FOREIGN KEY (`rus_id`) REFERENCES `rus` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `web` (`suspended`, `id`, `rus_id`, `url`, `web_id`, `created`, `active`) VALUES
(0, 1,  1,  'www.test.com', 01337,  '2021-05-18 22:27:54',  1);

dbfiddle

Best Answer

You can just place all the other columns in the GROUP BY. It's more verbose, but it does the job.

You should place all non-aggregated columns from the SELECT into the GROUP BY also. Although MariaDB and MySQL allow other columns in the SELECT, it is effectively an ANY(...) aggregate, and is not deterministic in many cases.
You need to pre-filter contact in the ON clause now, do not filter in the WHERE
It's unclear why you have extra partner and contact lookups, they seems to serve no purpose.

SELECT 
    s.id,
    e.exception,
    s.name,
    w.url,
    w.web_id,
    w.active,
    w.suspended,
    r.email,
    p.name AS partner,
    p.id AS partnerId,
    GROUP_CONCAT(c.name) AS contactNames,
    GROUP_CONCAT(c.tel) AS contactTels,
    GROUP_CONCAT(c.email) AS contactEmails
FROM
    service s
        JOIN
    web w ON s.web_id = w.id
        JOIN
    rus r ON w.rus_id = r.id
        JOIN
    partner p ON r.partner_id = p.id
        LEFT JOIN
    exception e ON e.service_id = s.id
        LEFT JOIN
    contact c ON c.partner_id = p.id AND
        (c.main = 1 OR c.important = 1)
GROUP BY
    s.id,
    e.exception,
    s.name,
    w.url,
    w.web_id,
    w.active,
    w.suspended,
    r.email,
    p.name,
    p.id;

Related Solutions

Mysql – #1093 – You can’t specify target table ‘R’ for update in FROM clause

Based on the previous question and this, the following should do what you want. As with Pieter's update it doesn't assume that the ID's are in sequential order, rather it uses the ReqTime to determine order.

UPDATE Visits R
  INNER JOIN (
    SELECT ID,
      TIME_TO_SEC(TIMEDIFF(
        (SELECT B.ReqTime
         FROM Visits B
         WHERE B.ReqTime > A.ReqTime
         ORDER BY B.ReqTime
         LIMIT 1
         )
        ,A.ReqTime)) Duration
    FROM Visits A
   ) N ON R.ID = N.ID
SET R.Duration = N.Duration
WHERE Duration IS NULL;

MariaDB Subquery Cache Scope and Settings – How to Configure

The cache needs to return the same results as running the subquery would, so the scope has to be limited by active transaction too.

But seeing that the cache can only be enabled or disabled as an optimizer feature and not configured otherwise, my guess is that it actually is instantiated once for each specific cacheable subquery for a query where the optimizer decides it is worth it.

That seems to be confirmed by: "It has a unique index over all parameters." - this is not doable if you store results for more than one subquery in the same table.

So it seems to me that the cache is instantiated once for each cacheable subquery and its lifetime would be limited by the outer query execution.

Edit: I checked some source code (disclaimer: I definitely do not fully understand the code as MariaDB is quite big and complex piece of software and my knowledge of C++ is far from expert level). The cache is implemented by the Expression_cache_tmptable. The table used to store the cached results is created in the init() method which is being called from some methods in Item_cache_wrapper and AFAIK lifespan of various Items is single (outer) query execution as different types of Items keep and manage different parts of the query (sort of expression tree if I understand it correctly).

From that I deduce that one instance of the subquery cache is created for each cacheable subquery at the time query is being "prepared" for execution. It is then initialized (and the temp table created) when it is first accessed during the query execution (so only if the subquery is actually executed at least once). The condition of "<0.2" is then checked (for each subquery independently) after 200 misses (that will probably be after processing some 200+ rows of the outer query) to see if the cache has any positive effect, and it is disabled early if it does not help. Then, if the cache table grows too much, it is checked again according to the conditions listed in the manual.

So the subquery cache is not tuneable, but seems to be made as unintrusive as possible. The optimizer will ever instantiate it for subqueries which can at least theoretically use it and it will be disabled early in the query execution if it does not prove useful.

If you are still worried about it slowing your queries down, you can disable it globally by configuring the optimizer_switch and then enable it only for selected queries by calling

SET SESSION optimizer_switch='subquery_cache=on';

before the query execution and optionally turn it off afterwards.

Best Answer

Related Solutions

Mysql – #1093 – You can’t specify target table ‘R’ for update in FROM clause

MariaDB Subquery Cache Scope and Settings – How to Configure

Related Question