0

I have this SQL query:

SELECT parent_category FROM category
JOIN
(
    SELECT category_id FROM venue
    JOIN
    (
        SELECT venue_id FROM city_venues
        WHERE city_id = 3
    ) AS venues 
    WHERE venues.venue_id = venue.venue_id
) AS venue_categories_list
WHERE venue_categories_list.category_id = category.category_id;

This command gives me exactly the results I want but I have noticed that it's execution is very slow (100-200ms) even with small datasets. Since I want this query to be part of a trigger, I want it to be as efficient as possible. Any ideas on how to make this faster ?

EDIT:

The tables related to this query are the ones below:

schema

CREATE TABLE queries:

-- -----------------------------------------------------
-- Table `app`.`category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`category` (
  `category_id` CHAR(24) NOT NULL,
  `category_name` VARCHAR(50) NOT NULL,
  `category_plural_name` VARCHAR(50) NOT NULL,
  `parent_category` CHAR(24) NOT NULL,
  `url_icon` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`category_id`),
  CONSTRAINT `fk_parent_category`
    FOREIGN KEY (`parent_category`)
    REFERENCES `app`.`category` (`category_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `app`.`venue`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`venue` (
  `venue_id` CHAR(24) NOT NULL,
  `venue_name` VARCHAR(100) NOT NULL,
  `address` VARCHAR(100) NULL,
  `lat` DECIMAL(16,14) NOT NULL,
  `lng` DECIMAL(16,14) NOT NULL,
  `category_id` CHAR(24) NOT NULL,
  PRIMARY KEY (`venue_id`),
  INDEX `fk_venue_category_idx` (`category_id` ASC) VISIBLE,
  CONSTRAINT `fk_venue_category`
    FOREIGN KEY (`category_id`)
    REFERENCES `app`.`category` (`category_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `app`.`city`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`city` (
  `city_id` INT NOT NULL AUTO_INCREMENT,
  `city_name` VARCHAR(50) NOT NULL,
  `lat` DECIMAL(16,14) NOT NULL,
  `lng` DECIMAL(16,14) NOT NULL,
  PRIMARY KEY (`city_id`),
  UNIQUE INDEX `city_name_UNIQUE` (`city_name` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `app`.`city_venues`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`city_venues` (
  `city_id` INT NOT NULL,
  `venue_id` CHAR(24) NOT NULL,
  PRIMARY KEY (`city_id`, `venue_id`),
  INDEX `fk_venue_idx` (`venue_id` ASC) VISIBLE,
  CONSTRAINT `fk_hasVenues_city`
    FOREIGN KEY (`city_id`)
    REFERENCES `app`.`city` (`city_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_hasVenues_venue`
    FOREIGN KEY (`venue_id`)
    REFERENCES `app`.`venue` (`venue_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

I use MySQL Workbench to generate the queries for creating the schema. I use InnoDB as the engine.

EXPLAIN Output:

explain

What I want to do with this query is to return all parent categories IDs' for each venue a city has. I care about duplicates as I want to count how many venues of each category does a city have. I would appreciate if you could give me hints about how I can do multiple counts also ( return columns with each category metrics).

11
  • Please add EXPLAIN of your query and create tables for all tables
    – nbk
    Apr 8 '20 at 14:38
  • @nbk Made an edit.
    – NickDelta
    Apr 8 '20 at 15:27
  • this doesn't show indexes or anything else useful for your qiestion , also use EXPLAIN your select statement
    – nbk
    Apr 8 '20 at 15:29
  • @nbk Edited again, indexes are now visible and explain added.
    – NickDelta
    Apr 8 '20 at 15:37
  • why do you prefer to use char(24) for id columns? Apr 8 '20 at 16:52
1

Here are some example queries that simplify the SQL, as well as provide solutions to what you are asking. As far as performance, you'll have to test them:

--RETURNS ALL PARENT CATEGORIES FOR A CITY
SELECT cats.parent_category 
FROM city_venues AS venues  
     JOIN venue ON venue.venue_id = venues.venue_id
     JOIN category AS cats ON venue.category_id = cats.category_id
WHERE venues.city_id = 3 

--COUNTS HOW MANY CATEGORIES IN EACH CITY
SELECT venues.city_id, COUNT(cats.parent_category)
FROM city_venues AS venues  
     JOIN venue ON venue.venue_id = venues.venue_id
     JOIN category AS cats ON venue.category_id = cats.category_id
WHERE venues.city_id = 3 
GROUP BY venues.city_id

--COUNTS THE VENUES IN EACH CATEGORY FOR A CITY 
--(Specified in the WHERE     statement)
--REMOVE THE "WHERE venues.city_id = 3" TO GET OVERALL 
--COUNT OF VENUES IN ALL CITIES
SELECT venues.city_id, cats.parent_category, COUNT(venue.venue_id)
FROM city_venues AS venues  
     JOIN venue ON venue.venue_id = venues.venue_id
     JOIN category AS cats ON venue.category_id = cats.category_id
WHERE venues.city_id = 3 
GROUP BY venues.city_id, cats.parent_category

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.