Poda de partição eficiente com ORDER BY na mesma coluna que PARTITION BY RANGE + LIMIT?

Eu configurei uma tabela no MariaDB (10.4.5, atualmente RC) com o InnoDB usando particionamento por uma coluna cujo valor é somente incrementado e novos dados são sempre inseridos no final. Nesse caso, o particionamento faz sentido para acelerar algumas consultas e manter partições novas/ativas em unidades rápidas e antigas/arquivadas em discos giratórios lentos. Para acelerações de inserção está funcionando muito bem! (Uma espécie de abordagem TimescaleDb, mas sem tempo e sem PostgreSQL.)

SELECTs por intervalo na mesma coluna também funcionam bem; ele só começará a ler (o índice de) as partições do intervalo especificado. Tudo legal até agora.

Agora, também tenho consultas que não têm uma cláusula nessa coluna, mas são ordenadas de forma decrescente por essa coluna (ou seja, dados novos primeiro), junto com um limite, que normalmente atingiria apenas uma ou duas partições mais recentes (rápidas, em cache índice). No entanto, parece que o MySQL/MariaDB começa a abrir partições do primeiro ao último, não importa qual seja a ordem especificada. É realmente tão burro? Além disso, não consegui encontrar mais ninguém com essa dúvida, o que me preocupa um pouco. (Às vezes significa que estou perdendo algo realmente óbvio.)

Para ficar mais concreto aqui - para testes, tenho a seguinte tabela:

CREATE TABLE `mytable` (
  `user_id` bigint(20) unsigned NOT NULL,
  `my_id` bigint(20) unsigned NOT NULL,
  `data` varbinary(123) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`my_id`),
  UNIQUE KEY `my_id_idx` (`my_id`)  -- I was hoping this one could help me
) ENGINE=InnoDB ROW_FORMAT=COMPACT
 PARTITION BY RANGE (`my_id`)
(PARTITION `p0` VALUES LESS THAN (10000000) ENGINE = InnoDB,
 PARTITION `p10M` VALUES LESS THAN (20000000) ENGINE = InnoDB,
 PARTITION `p20M` VALUES LESS THAN (30000000) ENGINE = InnoDB,
 PARTITION `p30M` VALUES LESS THAN (40000000) ENGINE = InnoDB,
 [...]
) 

E eu executo uma consulta como:

SELECT 
    user_id,
    my_id,
    LENGTH(data) AS data_len
FROM
    mytable
    -- tried to optimize with index hints:
    -- USE INDEX FOR ORDER BY (MY_ID_IDX)
    -- USE INDEX FOR ORDER BY (PRIMARY)
    -- USE INDEX FOR ORDER BY (MY_IDX, PRIMARY)
WHERE
    user_id = 1234567
ORDER BY my_id DESC
LIMIT 10;

Descobri que ele começa a procurar TODOS os dados user_id = 1234567primeiro, mostrando primeiro a carga pesada de E / S nos discos giratórios, depois finalmente chegando ao armazenamento rápido para obter o conjunto completo e cortando as últimas LIMIT 10linhas ... o que estavam todos em armazenamento rápido, então perdemos minutos de tempo para nada! Hum.

Meus dados são muito grandes e não podemos ter todos os índices na memória - contamos com 'o suficiente' do índice no disco para ser armazenado em cache na camada de armazenamento. Mas mesmo que todos os índices couberem no cache, os dados precisam vir de discos e alguns usuários têm uma quantidade ENORME de dados aqui (> 10 milhões de linhas) e é simplesmente ineficiente fazer essa classificação na memória assim. Então, espero encontrar uma maneira de fazer com que o MariaDB procure a última quantidade LIMIT de linhas e pare de ler.

Como humano, você começaria a procurar na última partição primeiro, porque ela ORDER BY my_id DESCe as partições mais recentes contêm os valores mais altos para ela. No entanto, como eu digo ao MySQL/MariaDB para fazer isso?

explain partitionsresultado (para todas as variantes USE INDEX listadas acima é o mesmo):

  select_type: SIMPLE
        table: mytable
   partitions: p0M,p10M,p20M,p30M, ... (~ hundred here)
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 9999999 (worst-case)
        Extra: Using where

Na verdade, ao contrário do que eu esperava, nem está tendo melhor desempenho se fizer a consulta em ordem crescente , usando a partição first-to-new. Ele ainda solicitará todos os índices de todas as partições e descobrirá que só precisava de um ...

Ouvi algo sobre um índice global para partições em versões futuras do MySQL, mas duvido que realmente ajude aqui, dado o tamanho enorme ..., e já recebeu a dica pelo próprio layout de particionamento no meu caso. A informação que encontro sobre 'remoção de partição' parece não ter relação com a ordenação das leituras; apenas sobre cláusulas na consulta.

Qualquer ajuda é apreciada.:-)

^{As partições mais novas serão criadas dinamicamente e não é realmente viável dar uma dica sobre uma partição específica. Minha situação é que as partições "mais recentes" são rápidas, "mais antigas" são "lentas", "mais antigas" são "super lentas" - assumindo que nada armazenado em cache na camada de armazenamento é demais. Além disso, estou usando um proxy (SPIDER) em uma máquina separada que deve fornecer aos clientes uma única interface para consulta, sem precisar saber sobre o layout de particionamento dos back-ends, então prefiro uma maneira de fazer isso ' automático'.}