MYSQL - Optimizing MAX(CASE WHEN ...) I have a table similar to the following (Updated: added a type column** Simplified the real query too much I guess)

CREATE TABLE versions(
   type INT NOT NULL,
   version INT NOT NULL,
   important BOOLEAN NOT NULL,
   PRIMARY KEY (type, version)
)


Running SELECT type, MAX(version) FROM versions GROUP BY type is very efficient and retrieves a single row per type from the database.

However, running SELECT MAX(CASE WHEN important=1 THEN version END) FROM versions GROUP BY type seems to generate a full table scan.

Since I have an index on version I expected MySQL to start scanning versions from the highest and stop when it finds the first that has "important=1". Usually, with my data this requires scanning just a few rows.

Looking at performance_schema.events_statements_history (ROWS_EXAMINED column) it seems like MySQL scans the entire table in order to retrieve the row I need.

Any ideas on how to improve the performance of this query will be much appreciated.
For the simple queries (without GROUOP BY), you can rewrite them as:

SELECT version 
FROM versions 
-- WHERE important = 1
ORDER BY version DESC
LIMIT 1 ;


and check if the execution plan does not do a table scan and using the index in the way that you expect. 

Additionally, you can add an index on (important, version).



For the GROUP BY type queries, an index on (important, type, version) would fit better. 

Rewriting that query with LIMIT 1 is not trivial as we want many maximum (one for each type). But if the index alone doesn't improve things much, here is a different query that will use the above composite index.

Performance is usually improved with this method when there are few distinct type values:

SELECT dt.type, v.version 
FROM 
    ( SELECT type 
      FROM versions 
      WHERE important = 1
      GROUP BY type
    ) AS dt
  -- LEFT
  JOIN versions AS v
  ON  v.important = 1
  AND v.type = dt.type 
  AND v.version = ( SELECT vi.version
                    FROM versions AS vi
                    WHERE vi.important = 1
                      AND vi.type = dt.type
                    ORDER BY vi.version DESC
                    LIMIT 1 
                  ) ;


There are variations you could try. For example, removing the important = 1  (only from inside the derived table dt) and using LEFT JOIN there. This would give you all types (and NULL values for the types that have no version marked as "important".

If you have a separate table, with all the types (i.e. PRIMARY KEY (type)), you could completely replace the dt with that table.