O. Jones's questions

No MySQL/MariaDB eu tenho esta tabela com linhas de comprimento fixo (sem VARCHAR, TEXT, etc)

CREATE TABLE trigram (
    id BIGINT(20) NOT NULL,
    trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (trigram, id) USING BTREE,
    INDEX id (id) USING BTREE
)
COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT;

A tabela tem dezenas de megarows e obtém consultas de produção neste formato

SELECT id FROM trigram 
 WHERE trigram IN ('dba', 'ba.', 'a.s', '.st', 'sta', 'tac', 'ack')
 GROUP BY ID HAVING COUNT(*) = 7

bem como INSERTs e DELETE FROM trigram WHERE id = 12345consultas de manutenção. Os índices são apropriados para os padrões de consulta da tabela.

Esta tabela é um índice trigrama do homem pobre. (Este pobre homem não pode atualizar para o postgreSQL e usar seus índices trigramas integrados, suspiro.) A consulta de exemplo procura ids que contenham strings 'dba.stack'. É muito mais rápido do que content_column LIKE '%dba.stack%'quando a tabela de trigramas é construída.

Editar: O que quero dizer com "melhor"? Mais rápido, mais confiável, menos liberação do buffer pool na produção, menos carga de manutenção para usuários que não são DBA.

Pergunta: Devo definir esta tabela de linhas de comprimento fixo com ROW_FORMAT=COMPACT? Ou é necessário DYNAMIC? Percebi que ocupa um pouco menos espaço em disco com o COMPACT.

Pergunta: Alguma outra sugestão ou aspecto de desempenho com que se preocupar?

Meus usuários (usuários do software WordPress.org) estão principalmente no MariaDB 10.3+, mas alguns estão no MySQL 8 e alguns estão no MySQL 5.7-. Não preciso oferecer suporte a material legado do Antelope ou MyISAM.

Outra edição:

Minha IN()consulta faz uma varredura de intervalo em um conjunto de dados de teste com 180 mil linhas na tabela. A JOINtabela UNION sugerida em uma resposta faz um loop aninhado. A varredura de alcance leva menos tempo. Verdadeiro no MariaDB 10.11, MySQL 8 e MySQL 5.7. Pelo que vale a pena. Parece que a otimização do skip-scan funciona muito bem.

Se uma coluna AUTO_INCREMENT sequencial não for também a chave primária de uma tabela MySQL, ela precisa ter seu próprio índice UNIQUE para garantir a integridade dos dados a longo prazo?

Estou trabalhando na reindexação de uma tabela MySQL específica para acelerar certas cláusulas WHERE muito comuns. A tabela é do CMS do WordPress . Sua definição fora da distribuição, simplificada, é essa.

CREATE TABLE wp_postmeta (
    meta_id BIGINT UNSIGNED AUTO_INCREMENT,
    post_id BIGINT UNSIGNED,
    meta_key VARCHAR(255),
    meta_value LONGTEXT,
    PRIMARY KEY (meta_id),
    INDEX post_id (post_id),
    INDEX meta_key (meta_key)
);

MariaDB / MySQL (InnoDB) agora usa índices clusterizados, então alterei o PK para este:

    PRIMARY KEY (post_id, meta_key, meta_id)

Isso significa que filtros WHERE como post_id = 42 AND meta_key = 'value'podem ser satisfeitos usando o índice clusterizado e a busca LONGTEXT. Está comprovado que resolve o problema de desempenho dos meus usuários.

A questão é esta: essa tabela reindexada também precisa de um índice exclusivo na coluna de ID de incremento automático para continuar funcionando corretamente a longo prazo? Ou posso omitir esse índice para economizar um pouco de espaço e desempenho de UPDATE/INSERT?

    UNIQUE INDEX meta_id (meta_id),

(Estou ciente de que pode haver designs de tabela muito melhores do que este. Mas minha atribuição atual não me permite alterar a tabela, apenas os índices.)

Em uma aplicação de produção (C# falando com SQL Server 2014 Standard) existe uma consulta que se parece com esta, abaixo. Na maioria das vezes, ele é executado em milissegundos. Mas, ocasionalmente (para certos valores de @Id), ele enlouquece e leva um minuto ou mais. Isso é mais longo que o tempo limite do aplicativo, portanto, o aplicativo falha para o usuário.

Nos casos "enlouquece", o conjunto de resultados retornado está corretamente vazio, como em muitos, mas não em todos os outros casos.

Felizmente, isso pode ser reproduzido nos ambientes de produção e desenvolvimento.

O desenvolvedor diz que remover "TOP 1" da consulta e garantir que o aplicativo consuma as linhas extras do conjunto de resultados elimina o problema de desempenho.

O planejador de consulta não sugere nenhum índice quando TOP 1está presente. (em desenvolvimento).

A alteração da consulta e a correção do aplicativo estão em andamento. A implantação demora um pouco.

Minha pergunta: existe alguma maneira acessível ao DBA de ajustar ou ajustar a instância de produção do SQL Server para superar esse problema antes que a alteração do aplicativo com a nova consulta seja lançada?

SELECT TOP 1
       subscription_id 
  FROM subscription AS sub
  JOIN billing_info AS bi ON bi.billing_info_id = sub.billing_info_id   
  JOIN person_group AS apg ON apg.person_id = bi.person_id
  JOIN pplan ON pplan.plan_id = sub.plan_id
  JOIN product ON product.product_id = [plan].product_id 
  JOIN product_attribute ON product_attribute.product_id = product.product_id 
 WHERE apg.group_id = @Id
   AND apg.start_date < GETDATE()
   AND (apg.end_date IS NULL OR apg.end_date > GETDATE()) 
   AND (sub.end_date IS NULL OR sub.end_date > GETDATE()) 
   AND product_attribute.attribute_type = 'special feature' 
   AND product_attribute.attribute_data = '1' 
 ORDER BY sub.start_date ASC;