0

Let's say I have this query:

(SELECT pets.id, "pets" AS "table" FROM pets WHERE name="Joey")
UNION ALL
(SELECT toys.id, "toys" AS "table" FROM (SELECT id, "pets" AS "table" FROM pets WHERE name="Joey") AS parentQuery, toys WHERE type="Ball" AND pet_id=parentQuery.id)
UNION ALL
(SELECT owners.id, "owners" AS "table" FROM (SELECT id, "pets" AS "table" FROM pets WHERE name="Joey") AS parentQuery, owners WHERE name="Issac" AND pet_id=parentQuery.id)

SQL Fiddle: http://sqlfiddle.com/#!9/1637a/1

The idea here is to get the ID columns for related rows in multiple one-to-many scenarios. A couple of questions:

  1. While I'm hopeful that MySQL optimizes to some extent with query caching the results for having the same initial query here embedded into the other UNION'ed queries as a sub-query, is it possible to safely and cleanly use SQL variables (or something) to reference the results from the first query in the other queries... in order to avoid repeating the query and any bundled parameters again for each additional UNION'ed query?
  2. (optional) If you think this is bad/reckless SQL design, please let me know. I'm sure this could be done with JOIN variants, but I'm just not sure if the performance would be better with a lot of joins than just splitting it up like this? I've heard differing opinions.

Thanks! I've seen this response already and think that something similar could work, but I've generally avoided SQL variables thus far, so I'm not sure how to structure it with UNION'ed queries: How to optimize huge query with repeated subqueries

Ben Guild
  • 4,881
  • 7
  • 34
  • 60
  • I feel like you could test the permutations as quickly as you could construct the question. – Strawberry Jul 18 '16 at 12:13
  • Well, my #1 question is most important here... avoiding re-bundling the initial query in the subsequent queries. Performance is good so far in testing. – Ben Guild Jul 18 '16 at 12:14
  • If performance is good delete the question. This is premature optimization. AFAIK. It's not possible to avoid rewriting that query because mysql doesn't have common table expressions. – e4c5 Jul 18 '16 at 12:22
  • 1
    Why would I delete the question? If want to answer that it cannot be done (the request... avoid repeating the query/parameters) then say that. Other people might have the same issue! – Ben Guild Jul 18 '16 at 13:39
  • Could you please share an **sql fiddle**? – 1000111 Jul 18 '16 at 14:43
  • @1000111 done, edited in above. – Ben Guild Jul 18 '16 at 14:57
  • @1000111 I tried to add some sample rows, but SQL Fiddle won't work. http://sqlfiddle.com/#!9/6b139 ... rows are here in this variant, but the SQL is missing from the first link shared above. I think the site may be experiencing technical difficulties. – Ben Guild Jul 18 '16 at 16:01

1 Answers1

1

I would like to add few more things.

1) Convert your query using inner join.

I personally think the INNER JOIN is better, because it is more readable. It shows better the relations between the tables. You got those relations in the join, and you do the filtering in the WHERE clause. This separation makes the query more readable. But this is a matter of personal taste.

So, based on this choice here's your converted query:

SELECT 
    pets.id, 
    "pets" AS "table" 
FROM pets 
WHERE name= "Joey" 

UNION ALL

SELECT 
    owners.id,
    "pets" AS "table"
FROM pets 
INNER JOIN owners ON pets.id = owners.pet_id
WHERE pets.`name` = "Joey"
AND owners.`name`= "Issac"

UNION ALL

SELECT 
    toys.id,
    "toys" AS "table"
FROM toys 
INNER JOIN pets ON toys.pet_id = pets.id
WHERE pets.Name="Joey"
AND toys.type="Ball";

2) Although your current query is performing well I think you can boost up performance more by creating some composite index in the tables.

  • pets table needs composite index on name,id.
  • toys table needs composite index on pet_id,type.
  • owners table need composite index on pet_id,name.

EDIT:

Here are the table structures of those three tables having the aforementioned indexes created.

-- ----------------------------
-- Table structure for `owners`
-- ----------------------------
DROP TABLE IF EXISTS `owners`;
CREATE TABLE `owners` (
  `id` bigint(20) DEFAULT NULL,
  `pet_id` bigint(20) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  KEY `Idx_owners_pet_id_name` (`pet_id`,`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Table structure for `pets`
-- ----------------------------
DROP TABLE IF EXISTS `pets`;
CREATE TABLE `pets` (
  `id` bigint(20) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  KEY `Idx_pets_name_pet_id` (`name`,`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `toys`
-- ----------------------------
DROP TABLE IF EXISTS `toys`;
CREATE TABLE `toys` (
  `id` bigint(20) DEFAULT NULL,
  `pet_id` bigint(20) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  KEY `Idx_toys_pet_id_type` (`pet_id`,`type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Note: Later you can check the query performance by EXPLAIN (MySQL). Be sure you have large set of data in those three tables. Because you cannot judge performance on small data set.

1000111
  • 13,169
  • 2
  • 28
  • 37