MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.36


CREATE TABLE vehicle 
(
  vin VARCHAR (50) NOT NULL PRIMARY KEY,
  model VARCHAR (30) NOT NULL,
  category VARCHAR (30) NOT NULL
);
CREATE TABLE sale
(
  sale_id INTEGER NOT NULL PRIMARY KEY
  -- nothing useful in sale - maybe rethink design?
);
CREATE TABLE vehicle_sale
(
  vs_vin   VARCHAR (50) NOT NULL,
  vs_sale_id INTEGER NOT NULL,
  vs_price INTEGER NOT NULL,
  
  CONSTRAINT vs_vin_sale_pk PRIMARY KEY (vs_vin, vs_sale_id), -- same vehicle could be
                                                              -- sold more than once!
  
  CONSTRAINT vs_vin_fk  FOREIGN KEY (vs_vin)     REFERENCES vehicle (vin),
  CONSTRAINT vs_s_id_fk FOREIGN KEY (vs_sale_id) REFERENCES sale (sale_id)
);
INSERT INTO vehicle VALUES
('v1',  'm1',  'car'),
('v2',  'm2',  'car'),
('v3',  'm2',  'car'),
('v4',  'm2',  'car'),
('v5',  'm5',  'elmo'),
('v6',  'm6',  'elmo'),
('v7',  'm6',  'elmo'),
('v8',  'm6',  'elmo'),
('v9',  'm9',  'truck'),
('v10', 'm10', 'truck'),
('v11', 'm10', 'truck'),
('v12', 'm10', 'truck'),
('v13', 'm10', 'truck'),
('v14', 'm10', 'truck');



Records: 14  Duplicates: 0  Warnings: 0


INSERT INTO sale
VALUES
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (12), (13), (14);

Records: 13  Duplicates: 0  Warnings: 0


INSERT INTO vehicle_sale
VALUES
('v1',  1,   100), -- m1
('v2',  2,   200), -- m2
('v3',  3,   300), -- m2
('v4',  4,   400), -- m2

('v5',  5,   500), -- m5
('v6',  6,   600), -- m6
('v7',  7,   700), -- m6

('v9',  9,   800),  -- m9
('v12', 12,  900), -- m10
('v13', 13, 1000), -- m10
('v14', 14, 1100); -- m10

Records: 11  Duplicates: 0  Warnings: 0


  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(v.model) DESC) AS rn
  FROM vehicle_sale vs
    INNER JOIN vehicle v
      ON vs.vs_vin = v.vin
    INNER JOIN sale s
      ON vs.vs_sale_id = s.sale_id
  GROUP BY v.category, v.model
  ORDER BY v.category, cnt ASC;


category
model
cnt
rn


car
m1
1
2

car
m2
3
1

elmo
m5
1
2

elmo
m6
2
1

truck
m9
1
2

truck
m10
3
1


SELECT a.category, a.model, a.cnt 
FROM
(
  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(v.model) DESC) AS rn
  FROM vehicle_sale vs
    INNER JOIN vehicle v
      ON vs.vs_vin = v.vin
    INNER JOIN sale s
      ON vs.vs_sale_id = s.sale_id
  GROUP BY v.category, v.model
  ORDER BY v.category, cnt DESC
) AS a
WHERE a.rn = 1
ORDER BY a.category;


category
model
cnt


car
m2
3

elmo
m6
2

truck
m10
3


SELECT 
  a.category, a.model, a.cnt 
  -- , y.category, y.mcnt  -- the y values are not required anymore! Left in for explanation
FROM
(
  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt
  FROM vehicle_sale vs
    INNER JOIN vehicle v
      ON vs.vs_vin = v.vin
    INNER JOIN sale s
      ON vs.vs_sale_id = s.sale_id
  GROUP BY v.category, v.model
  ORDER BY v.category, cnt DESC
) AS a
JOIN
(
  SELECT category, MAX(cnt) AS mcnt
  FROM
  (
    SELECT 
      v.category, v.model,
      COUNT(v.model) AS cnt
    FROM vehicle_sale vs
      INNER JOIN vehicle v
        ON vs.vs_vin = v.vin
      INNER JOIN sale s
        ON vs.vs_sale_id = s.sale_id
    GROUP BY v.category, v.model
    ORDER BY v.category, cnt DESC
  ) AS x
  GROUP BY category
  ORDER BY category
) AS y
ON a.category = y.category AND a.cnt = y.mcnt
ORDER BY a.category, a.cnt;


category
model
cnt


car
m2
3

elmo
m6
2

truck
m10
3


EXPLAIN ANALYZE
SELECT a.category, a.model, a.cnt 
FROM
(
  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(v.model) DESC) AS rn
  FROM vehicle_sale vs
    INNER JOIN vehicle v
      ON vs.vs_vin = v.vin
    INNER JOIN sale s
      ON vs.vs_sale_id = s.sale_id
  GROUP BY v.category, v.model
  ORDER BY v.category, cnt DESC
) AS a
WHERE a.rn = 1
ORDER BY a.category;


EXPLAIN


-> Sort: a.category  (cost=0.485..0.485 rows=0) (actual time=0.22..0.22 rows=3 loops=1)
    -> Index lookup on a using <auto_key0> (rn=1)  (cost=0.35..0.385 rows=1.1) (actual time=0.215..0.216 rows=3 loops=1)
        -> Materialize  (cost=0..0 rows=0) (actual time=0.213..0.213 rows=6 loops=1)
            -> Sort: v.category, cnt DESC  (actual time=0.201..0.201 rows=6 loops=1)
                -> Table scan on <temporary>  (cost=2.5..2.5 rows=0) (actual time=0.182..0.183 rows=6 loops=1)
                    -> Temporary table  (cost=0..0 rows=0) (actual time=0.182..0.182 rows=6 loops=1)
                        -> Window aggregate: row_number() OVER (PARTITION BY v.category ORDER BY count(v.model) desc )   (actual time=0.173..0.176 rows=6 loops=1)
                            -> Sort: v.category, cnt DESC  (actual time=0.17..0.17 rows=6 loops=1)
                                -> Table scan on <temporary>  (actual time=0.138..0.139 rows=6 loops=1)
                                    -> Aggregate using temporary table  (actual time=0.137..0.137 rows=6 loops=1)
                                        -> Nested loop inner join  (cost=9.05 rows=11) (actual time=0.0248..0.11 rows=11 loops=1)
                                            -> Nested loop inner join  (cost=5.2 rows=11) (actual time=0.0194..0.073 rows=11 loops=1)
                                                -> Covering index scan on vs using vs_s_id_fk  (cost=1.35 rows=11) (actual time=0.0112..0.0173 rows=11 loops=1)
                                                -> Single-row covering index lookup on s using PRIMARY (sale_id=vs.vs_sale_id)  (cost=0.259 rows=1) (actual time=0.00276..0.00278 rows=1 loops=11)
                                            -> Single-row index lookup on v using PRIMARY (vin=vs.vs_vin)  (cost=0.259 rows=1) (actual time=0.00309..0.00314 rows=1 loops=11)



EXPLAIN ANALYZE
SELECT 
  a.category, a.model, a.cnt
  -- , y.category, y.mcnt  -- the y values are not required anymore! Left in for explanation
FROM
(
  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt
  FROM vehicle_sale vs
    INNER JOIN vehicle v
      ON vs.vs_vin = v.vin
    INNER JOIN sale s
      ON vs.vs_sale_id = s.sale_id
  GROUP BY v.category, v.model
  ORDER BY v.category, cnt DESC
) AS a
JOIN
(
  SELECT category, MAX(cnt) AS mcnt
  FROM
  (
    SELECT 
      v.category, v.model,
      COUNT(v.model) AS cnt
    FROM vehicle_sale vs
      INNER JOIN vehicle v
        ON vs.vs_vin = v.vin
      INNER JOIN sale s
        ON vs.vs_sale_id = s.sale_id
    GROUP BY v.category, v.model
    ORDER BY v.category, cnt DESC
  ) AS x
  GROUP BY category
  ORDER BY category
) AS y
ON a.category = y.category AND a.cnt = y.mcnt
ORDER BY a.category, a.cnt;


EXPLAIN


-> Nested loop inner join  (cost=8.1 rows=0) (actual time=0.346..0.351 rows=3 loops=1)
    -> Sort: a.category, a.cnt  (cost=2.6..2.6 rows=0) (actual time=0.165..0.166 rows=6 loops=1)
        -> Table scan on a  (cost=2.5..2.5 rows=0) (actual time=0.146..0.147 rows=6 loops=1)
            -> Materialize  (cost=0..0 rows=0) (actual time=0.146..0.146 rows=6 loops=1)
                -> Sort: v.category, cnt DESC  (actual time=0.137..0.137 rows=6 loops=1)
                    -> Table scan on <temporary>  (actual time=0.126..0.127 rows=6 loops=1)
                        -> Aggregate using temporary table  (actual time=0.125..0.125 rows=6 loops=1)
                            -> Nested loop inner join  (cost=9.05 rows=11) (actual time=0.0377..0.0999 rows=11 loops=1)
                                -> Nested loop inner join  (cost=5.2 rows=11) (actual time=0.03..0.0611 rows=11 loops=1)
                                    -> Covering index scan on vs using vs_s_id_fk  (cost=1.35 rows=11) (actual time=0.0201..0.0258 rows=11 loops=1)
                                    -> Single-row covering index lookup on s using PRIMARY (sale_id=vs.vs_sale_id)  (cost=0.259 rows=1) (actual time=0.00291..0.00294 rows=1 loops=11)
                                -> Single-row index lookup on v using PRIMARY (vin=vs.vs_vin)  (cost=0.259 rows=1) (actual time=0.00321..0.00326 rows=1 loops=11)
    -> Covering index lookup on y using <auto_key0> (category=a.category, mcnt=a.cnt)  (cost=0.259..0.518 rows=2) (actual time=0.0304..0.0305 rows=0.5 loops=6)
        -> Materialize  (cost=0..0 rows=0) (actual time=0.176..0.176 rows=3 loops=1)
            -> Sort: x.category  (actual time=0.169..0.169 rows=3 loops=1)
                -> Table scan on <temporary>  (actual time=0.153..0.154 rows=3 loops=1)
                    -> Aggregate using temporary table  (actual time=0.153..0.153 rows=3 loops=1)
                        -> Table scan on x  (cost=2.5..2.5 rows=0) (actual time=0.142..0.142 rows=6 loops=1)
                            -> Materialize  (cost=0..0 rows=0) (actual time=0.141..0.141 rows=6 loops=1)
                                -> Sort: v.category, cnt DESC  (actual time=0.136..0.136 rows=6 loops=1)
                                    -> Table scan on <temporary>  (actual time=0.111..0.112 rows=6 loops=1)
                                        -> Aggregate using temporary table  (actual time=0.11..0.11 rows=6 loops=1)
                                            -> Nested loop inner join  (cost=9.05 rows=11) (actual time=0.0283..0.0885 rows=11 loops=1)
                                                -> Nested loop inner join  (cost=5.2 rows=11) (actual time=0.0235..0.053 rows=11 loops=1)
                                                    -> Covering index scan on vs using vs_s_id_fk  (cost=1.35 rows=11) (actual time=0.0177..0.0226 rows=11 loops=1)
                                                    -> Single-row covering index lookup on s using PRIMARY (sale_id=vs.vs_sale_id)  (cost=0.259 rows=1) (actual time=0.00248..0.00251 rows=1 loops=11)
                                                -> Single-row index lookup on v using PRIMARY (vin=vs.vs_vin)  (cost=0.259 rows=1) (actual time=0.00293..0.00298 rows=1 loops=11)



SET PROFILING = 1;
SELECT 
  a.category, a.model, a.cnt
  -- , y.category, y.mcnt  -- the y values are not required anymore! Left in for explanation
FROM
(
  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt
  FROM vehicle_sale vs
    INNER JOIN vehicle v
      ON vs.vs_vin = v.vin
    INNER JOIN sale s
      ON vs.vs_sale_id = s.sale_id
  GROUP BY v.category, v.model
  ORDER BY v.category, cnt DESC
) AS a
JOIN
(
  SELECT category, MAX(cnt) AS mcnt
  FROM
  (
    SELECT 
      v.category, v.model,
      COUNT(v.model) AS cnt
    FROM vehicle_sale vs
      INNER JOIN vehicle v
        ON vs.vs_vin = v.vin
      INNER JOIN sale s
        ON vs.vs_sale_id = s.sale_id
    GROUP BY v.category, v.model
    ORDER BY v.category, cnt DESC
  ) AS x
  GROUP BY category
  ORDER BY category
) AS y
ON a.category = y.category AND a.cnt = y.mcnt
ORDER BY a.category, a.cnt;


category
model
cnt


car
m2
3

elmo
m6
2

truck
m10
3


SELECT a.category, a.model, a.cnt 
FROM
(
  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(v.model) DESC) AS rn
  FROM vehicle_sale vs
    INNER JOIN vehicle v
      ON vs.vs_vin = v.vin
    INNER JOIN sale s
      ON vs.vs_sale_id = s.sale_id
  GROUP BY v.category, v.model
  ORDER BY v.category, cnt DESC
) AS a
WHERE a.rn = 1
ORDER BY a.category;


category
model
cnt


car
m2
3

elmo
m6
2

truck
m10
3


SHOW PROFILES;


Query_ID
Duration
Query


1
0.00093200
SELECT 
  a.category, a.model, a.cnt
  -- , y.category, y.mcnt  -- the y values are not required anymore! Left in for explanation
FROM
(
  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt
  FROM vehicle_sale vs
    INNER JOIN vehicle v
      ON vs.vs_vin = v.vin
    INNER JOIN sale s
     

2
0.00051450
SELECT a.category, a.model, a.cnt 
FROM
(
  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(v.model) DESC) AS rn
  FROM vehicle_sale vs
    INNER JOIN vehicle v
      ON vs.vs_vin = v.vin
    INNER JOIN sale s
      ON vs.vs_sal


SET PROFILING = 0;
Query_ID	Duration	Query
1	0.00093200	SELECT a.category, a.model, a.cnt -- , y.category, y.mcnt -- the y values are not required anymore! Left in for explanation FROM ( SELECT v.category, v.model, COUNT(v.model) AS cnt FROM vehicle_sale vs INNER JOIN vehicle v ON vs.vs_vin = v.vin INNER JOIN sale s
2	0.00051450	SELECT a.category, a.model, a.cnt FROM ( SELECT v.category, v.model, COUNT(v.model) AS cnt, ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(v.model) DESC) AS rn FROM vehicle_sale vs INNER JOIN vehicle v ON vs.vs_vin = v.vin INNER JOIN sale s ON vs.vs_sal