SQL query optimization with sophisticated filtering

Question

Please note that the question below is specifically for MySQL .

Imagine a table called Carswith the following structure (we can ignore the lack of proper key restrictions, etc., since this does not apply to my question):

CREATE TABLE Cars
(
  id Integer,
  maker_id Integer,
  status_id Integer,
  notes Varchar(100)
);

Now imagine loading some test data like this:

INSERT INTO Cars
(id, maker_id, status_id, notes)
VALUES
(1, 1001, 0, 'test1'),
(2, 1001, 0, 'test2'),
(3, 1001, 0, 'test3'),
(4, 1002, 0, 'test4'),
(5, 1002, 0, 'test5'),
(6, 1002, 1, 'test6'),
(7, 1002, 1, 'test7'),
(8, 1002, 2, 'test8'),
(9, 1003, 3, 'test9'),
(10, 1003, 3, 'test10'),
(11, 1003, 4, 'test11'),
(12, 1003, 4, 'test12'),
(13, 1003, 5, 'test13'),
(14, 1003, 5, 'test14')

There are 14 entries with 3 DISTINCTvalues ​​in maker_id(1001, 1002, 1003) and 6 DISTINCTvalues ​​in status_id(0,1,2,3,4,5).

Now imagine a couple DISTINCT( maker_id, status_id).

SELECT DISTINCT maker_id, status_id FROM Cars;

Here is a link to an example in SQL Fiddle: http://sqlfiddle.com/#!9/cb1c7/2

This results in the following entries ( maker_id, status_id):

• (1001,0)
• (1002,0)
• (1002, 1)
• (1002, 2)
• (1003, 3)
• (1003, 4)
• (1003, 5)

, , :

maker_id (, 1001) DISTINCT (maker_id, status_id), . : (1001, 0).

maker_id 1 DISTINCT (maker_id, status_id), , , status_id 0. : (1002, 1), (1002, 2), (1003, 3), (1003, 4) (1003, 5).

, (1002, 0).

- , / ( )? .

:

SELECT
  subq.maker_id,
  subq.status_id
FROM
(
  SELECT DISTINCT
    maker_id,
    status_id,
    (SELECT COUNT(*) FROM Cars WHERE maker_id = c.maker_id AND status_id != 0 GROUP BY maker_id) AS counter
  FROM Cars AS c
) AS subq

WHERE
  subq.counter IS NULL
  OR (subq.counter IS NOT NULL AND subq.status_id != 0)
;

SQL Fiddle: http://sqlfiddle.com/#!9/cb1c7/3

Answer 1

, . , . , .

GROUP BY ( ESPECIALLY, MySQL . ( GROUP BY status_id maker_id.)

, . , , , .

, :

NOT TESTED

 SELECT c.maker_id
      , c.status_id
   FROM Cars c
  WHERE c.status_id > 0

 UNION ALL

 SELECT d.maker_id
      , d.status_id
   FROM Cars d
   LEFT
   JOIN Cars e
     ON e.maker_id = d.maker_id
    AND e.status_id > 0
  WHERE e.maker_id IS NULL  
    AND d.status_id = 0

, , , .

.

..  ON Cars (maker_id, status_id)

, EXPLAIN " " Extra. " filesort".

, ( ).

SELECT ... , status_id . . , , ,

... ON Cars (status_id, maker_id)

. , , .

SELECT . status_id , "" , , maker_id status_id, ,

(LEFT JOIN), status_id=0 . - WHERE, , . , , . maker_id, status_id=0.

, NOT EXISTS, -. , , . SELECT ( UNION ALL)

 SELECT d.maker_id
      , d.status_id
   FROM Cars d
  WHERE d.status_id = 0
    AND NOT EXISTS
        ( SELECT 1
            FROM Cars e
           WHERE e.maker_id = d.maker_id
             AND e.status_id > 0
        )

, -.

: ALL. UNION ALL . ALL, , MySQL " " .

. UNION ALL, OR , UNION ALL. MySQL, , OR, , "" . UNION ALL, , .

Answer 2

:)

select
    distinct c1.maker_id, c1.status_id
from
    Cars AS c1
where
    c1.status_id!=0
    or c1.maker_id not in (
        select distinct c2.maker_id
        from Cars AS c2
        where c2.status_id!=0
    )

Answer 3

...

... ... : - , .

. , " " . bloatload io to/tmp. SSD, ?

.

  SELECT t.maker_id
       , t.status_id
    FROM ( SELECT IF(s.status_id=0 AND s.maker_id=@p_maker,NULL,s.status_id) AS status_id
                , @p_maker := s.maker_id AS maker_id
             FROM ( SELECT @p_maker := NULL ) i
            CROSS
             JOIN ( SELECT c.maker_id
                         , c.status_id
                      FROM Cars c
                     GROUP BY c.maker_id DESC, c.status_id DESC 
                     ORDER BY c.maker_id DESC, c.status_id DESC
                  ) s
         ) t
   WHERE t.status_id IS NOT NULL

Answer 4

.. :

  SELECT maker_id,
         status_id
    FROM cars  
   WHERE status_id != 0
GROUP BY maker_id, status_id
   UNION ALL
  SELECT maker_id,
         MAX(status_id) max_status_id
    FROM cars  
GROUP BY maker_id
  HAVING max_status_id = 0