Perguntas[query-performance](dba)

Temos uma tabela #ValidCode com uma lista de códigos válidos como: 'A', 'B', 'C', etc. Outra tabela chamada #SourceData com dados de entrada — que vêm como uma combinação de tokens válidos e inválidos (às vezes duplicados).

Ex:

• 'A;B;C' ( válido )
• 'A;A;A;A;A;B' ( Válido )
• 'ad;df;A;B' ( inválido )

Tentando encontrar uma abordagem de consulta ótima para processar essas strings para encontrar linhas válidas em #SourceData . Veja o exemplo abaixo:

DROP TABLE IF EXISTS #ValidCode
GO
CREATE TABLE #ValidCode
(
      ID        INT             IDENTITY(1,1)
    , Code      CHAR(1)
)
INSERT INTO #ValidCode (Code) VALUES ('A'), ('B'), ('C')
GO
DROP TABLE IF EXISTS #SourceData 
GO
CREATE TABLE #SourceData 
(
      ID        INT             IDENTITY(1,1)
    , Codes     VARCHAR(500)
    , Is_Valid  BIT
    , Is_Split  BIT
)

INSERT INTO #SourceData (Codes) 
VALUES    ('A;B;C')
        , ('B;A')
        , ('B;B;B;C;C;A;A;B')
        , ('B;Z;1')
        , ('B;ss;asd')


SELECT * FROM #ValidCode
SELECT * FROM #SourceData

A consulta processaria os dados na tabela #SourceData e atualizaria o sinalizador Is_Valid para que eles pudessem ser consumidos no processo subsequente.

Regras :

• Cada token deve ser válido para que toda a linha da coluna seja válida (linhas 1 a 3)
• Mesmo que um token seja inválido, o valor da linha inteira será inválido (linhas 4 e 5)

Então, esta é a saída preferida:

Abordagem atual : Faça um loop por cada linha em #SourceData e divida-as no delimitador ';', depois compare-as com a tabela #ValidCode . Se todos os tokens forem individualmente válidos, marque a linha em #SourceData como válida ( flag Is_Valid ). Caso contrário, marque como inválida. A WHILEabordagem de loop funciona, mas é lenta.

O #SourceData pode ter até 3 milhões de linhas. Com cada linha tendo várias combinações de valores válidos duplicados ('A;A;A;A') e inválidos ('A;as;sdf;B')

Existe uma abordagem melhor?

Obrigado!

Tenho uma tabela com a seguinte estrutura:

create table TEST_TAB
(
  activity_type CHAR(1),
  tracking_code NUMBER,
  eff_date      DATE
)

Dados de amostra para esta tabela:

insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('01-11-2020', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('02-01-2024', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('01-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('02-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('03-08-2023', 'dd-mm-yyyy'));

Este é apenas um dado de amostra e a quantidade de dados reais na tabela original é de quase 400 milhões de registros. O que preciso fazer é que, para cada grupo de activity_type, tracking_code, preciso manter o registro que tem o maior "eff_date" e excluir o restante. Então, para o , activity_type=A and tracking_code = 1preciso manter um com eff_date = 1/2/2024e excluir o outro. O que tenho por enquanto é a consulta abaixo:

delete from test_tab
 where rowid in (select rid
                   from (select rowid as rid,
                                row_number() over(partition by activity_type, tracking_code order by eff_date desc) as row_num
                           from test_tab
                           )
                  where row_num > 1
                  )

No entanto, isso parece muito lento. Você poderia sugerir alguma solução melhor? A tabela original é particionada em eff_date e tem índice nas duas colunas restantes. Outro ponto é que pode haver mais de um intervalo de um ano entre eff_dates de cada registro em um único grupo.

desde já, obrigado

Tenho uma consulta que atinge uma tabela de 1,4 bilhão de linhas.

Tudo funciona bem com essa consulta, em menos de um segundo.

Então, uma vez a cada 5 dias, mais ou menos, as estatísticas de atualização automática entram em ação e a consulta começa a ser executada por quase 60 segundos. Ela adota um plano diferente.

Verifiquei e quando as estatísticas de atualização automática são ativadas, a taxa de amostragem é inferior a 1% da tabela.

Agora, minha pergunta é: uma atualização automática de estatísticas pode fazer com que o otimizador escolha um plano pior do que o anterior?

Posso ver que as estimativas do plano ruim são muito diferentes das do plano bom.

Bom plano:

Plano Ruim:

As estimativas estão claramente erradas para o plano ruim e, ainda assim, a quantidade de linhas retornadas pela consulta é a mesma.

Neste ponto, estou tentando confirmar se uma atualização automática de estatísticas pode fazer isso?

O que estou tentando agora é fazer uma atualização de estatísticas com uma amostra de 10% e ver o que acontece.

Atualização: Concluí uma atualização de estatísticas com taxa de amostragem de 10% e as consultas estão indo bem novamente.

Tornei isso uma tarefa a ser executada regularmente.

(Desculpas pela postagem longa - primeira postagem no SE)

Tenho usado o tipo hierarchyid em vários aplicativos por cerca de 10 anos e, no geral, estou satisfeito com o desempenho. Ocasionalmente, me deparei com relatos de desempenho ruim com hierarchyid e sempre presumi que era devido a índices ou consultas configurados incorretamente que poderiam ser otimizados. Mas agora eu mesmo me deparei com o problema.

Azure SQL, nível de compatibilidade de banco de dados definido como 160 (SQL Server 2022).

Tenho a seguinte tabela:

create table [Dim]
(
    [Key] [int] not null,
    [Path] [hierarchyid] null,
    [Name] [nvarchar](256) null,
    [ParentKey] [int] null,
    [SortOrder] [int] null,
    [IsLeaf] [bit] null,
    constraint PK_Dim primary key clustered ([Key]),
    index IX_Dim_Name unique ([Name]) include ([Path]),
    index IX_Dim_ParentKey unique ([ParentKey], [SortOrder]),
    index IX_Dim_Path ([Path]),
    index IX_Dim_Leaf ([IsLeaf], [Path])
)

A tabela é projetada para modelar uma hierarquia pai-filho. O campo Path reflete a hierarquia, com base no Path pai e no SortOrder do membro atual. Há 10.010 registros no conjunto de dados de teste com uma profundidade de hierarquia máxima de 8. Executamos a seguinte consulta:

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[Path]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[Path]
    from [Dim] d
    inner join [Dim] p on (d.[Path].IsDescendantOf(p.[Path]) = 1)
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[Path].IsDescendantOf(r.[Path]) = 1
        and d.[IsLeaf] = 1);

A consulta leva 14 segundos para ser executada e retorna 3.053 linhas. A subconsulta retorna 1.298 linhas. Abaixo está o plano de execução: e um link: https://www.brentozar.com/pastetheplan/?id=SydK3qIk1l

que parece muito com o que eu esperava, com exceção da varredura de índice em IX_Dim_Leaf, que lê 6.861.228 linhas.

Agora, se adicionarmos uma coluna adicional à tabela que contém a representação de string do campo Caminho e os dois índices correspondentes no novo campo:

alter table [Dim] add [PathStr] varchar(256);
go
update [Dim] set [PathStr] = [Path].ToString();
create index [IX_Dim_PathStr] on [Dim] ([PathStr]);
create index [IX_Dim_LeafStr] on [Dim] ([IsLeaf], [PathStr]);
go

e então reescreva a consulta para usar PathStr em vez de Path:

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[PathStr]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[PathStr]
    from [Dim] d
    inner join [Dim] p on (d.[PathStr] like p.[PathStr] + '%')
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[PathStr] like r.[PathStr] + '%'
    and d.[IsLeaf] = 1);

A nova consulta é executada em 0,064 segundos. O plano de execução está abaixo: e o link: https://www.brentozar.com/pastetheplan/?id=Sy5oT5Uyye

Na verdade, é mais complexo que o primeiro plano de consulta, pois o novo campo não está incluído no índice IX_Dim_Name e é preciso adicionar a string '%', mas a grande diferença está na varredura do índice externo, onde apenas 3.053 linhas são lidas em vez de 6,8 milhões.

Não faz sentido para mim que um campo de string tenha um desempenho melhor do que um campo hierarchyid que teoricamente é otimizado para esse tipo de consulta hierárquica. Estou fazendo algo errado ou o SQL Server é simplesmente incapaz de lidar com hierarchyids em subconsultas e devemos continuar com um campo de string?

Observação: armazenar os resultados da subconsulta em uma tabela var e, em seguida, unir a tabela var com a tabela Dim tem um desempenho marginalmente melhor ao usar hierarchyid, mas infelizmente isso não é uma opção.

Edição: seguindo a sugestão do Charlieface abaixo, também tentei esta consulta:

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[Path]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[Path]
    from [Dim] d
    inner join [Dim] p on (d.[Path].IsDescendantOf(p.[Path]) = 1)
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[Path].GetAncestor(1) = r.[Path]
        and d.[IsLeaf] = 1);

que executa em 38 ms ( execução do plano ). Parece que apenas IsDescendantOf() é um problema.

Se eu estiver consultando um banco de dados para selecionar todos os registros que têm um VARCHAR()campo igual a um parâmetro de consulta. Há alguma vantagem de velocidade em fazer uma pesquisa linear linha por linha de um "banco de dados" de arquivo simples? Se sim, como essa aceleração é realizada?

Eu tenho uma tabela assim:

Quero extrair informações do cliente da coluna "Nota" para colocá-las na coluna "Cliente" com apenas uma consulta SQL.

Dei alguns exemplos que representam os milhares de linhas reais que tenho. Primeiro fiz esta consulta:

SELECT
    CASE
        WHEN posclient != 0 THEN SUBSTRING(
            Note,
            posclient + LENGTH('Client: '),
            LOCATE('\n', Note, posclient + LENGTH('Client: ')) - (posclient + LENGTH('Client: '))
        )
        WHEN poscustomer != 0 THEN SUBSTRING(
            Note,
            poscustomer + LENGTH('Customer : '),
            LOCATE('\n', Note, poscustomer + LENGTH('Customer : ')) - (poscustomer + LENGTH('Customer : '))
        )
        WHEN poscontact != 0 THEN SUBSTRING(
            Note,
            poscontact + LENGTH('Contact : '),
            LOCATE('\n', Note, poscontact + LENGTH('Contact : ')) - (poscontact + LENGTH('Contact : '))
        )
        ELSE ''
    END AS client,
    Id
FROM (SELECT Id, Note,
        LOCATE('Customer : ', Note) as poscustomer,
        LOCATE('Client: ', Note) as posclient,
        LOCATE('Contact : ', Note) as poscontact
    FROM myTable) x 
WHERE (poscustomer != 0 OR posclient != 0 OR poscontact != 0)

Como há código duplicado (a WHENparte), é difícil de ler e não fica muito bom se eu tiver outra maneira de adicionar.

E eu tenho: há pelo menos 6 maneiras de escrever o cliente. Quero otimizar esta parte:

WHEN poscontact != 0 THEN SUBSTRING(
   Note,
   poscontact + LENGTH('Contact : '),
   LOCATE('\n', Note, poscontact + LENGTH('Contact : ')) - (poscontact + LENGTH('Contact : '))
)

Para ser "genérico", mas não sei como, pois não estou em linguagem de programação. Em php, isso seria fácil, mas não posso usar linguagem de programação, tenho que usar apenas uma consulta.

Uma coisa que pode ser muito útil: há apenas UMA maneira de escrever o cliente para cada linha. Ele sempre conterá apenas um.

Como posso fazer?

Eu trabalho com PostgreSQL 12 e tenho tabelas particionadas que estou tentando agrupar. Cada partição tem aproximadamente o mesmo tamanho em termos de GB. No entanto, o desempenho pode variar significativamente, variando de 5 a 1.000 minutos de tempo de cluster por partição.

Estou agrupando várias partições em cluster em paralelo e meu banco de dados não está recebendo nenhuma solicitação além das solicitações CLUSTER.

Não entendo completamente os meandros do processo CLUSTER nos bastidores. O que poderia explicar variações tão grandes no tempo de agrupamento e o que posso fazer para melhorar o desempenho?

EDIT: Para ser mais preciso, tenho uma partição de 34 GB agrupada em cluster em 13 minutos e uma partição de 56 GB agrupada em cluster em 1288 minutos (quase um dia). Esses valores de tamanho são o tamanho antes do cluster. Estou recalculando os tamanhos.

Consulta usada para recuperar o tamanho:

SELECT table_name, 
       pg_size_pretty(pg_total_relation_size(table_schema || '."' || table_name || '"')) AS size
FROM information_schema.tables
WHERE table_schema = 'partitionschema' and table_name like 'mytable_%' order by table_name;

Eu não fiz ANALYZE ou VACUUM antes de agrupar.

Tenho E/S de disco de 900 MB/s e 128 GB de RAM (embora apenas 40 pareçam ser usados ​​de acordo com meu painel do datadog). Também não parece que meus processadores sejam uma limitação.

Tenho 300 partições para agrupar, em cada etapa do processo, estou tentando agrupar 8 partições em paralelo. Cada uma dessas partições está em ~ 50 GB

Algumas outras informações:

• manutenção_work_mem: 4 GB
• trabalho_meme: 64 MB
• buffer_compartilhado: 32 GB
• max_wal_size: 4 GB

Suponha que eu tenha um esquema como o seguinte:

-- Many rows
CREATE TABLE t1(i INTEGER PRIMARY KEY, c1 INTEGER, c2 INTEGER);

-- t1's rows with c1 even
CREATE VIEW t1_filtered(i, c1, c2) AS
  SELECT i, c1, c2 FROM t1 WHERE c1 % 2 == 0;
-- The real WHERE clause is slightly more complex.

Suponha que essa tabela t1contenha alguns milhões de linhas:

INSERT INTO t1(i, c1, c2)
  SELECT value, random(), random() FROM generate_series(1, 5000000);

Suponha que eu queira obter o índice da linha t1que tem o par mais alto c1e a contagem de linhas com par c1que também tem par c2:

SELECT
  (SELECT i FROM t1_filtered ORDER BY c1 DESC LIMIT 1),
  (SELECT count(*) FROM t1_filtered WHERE c2 % 2 == 0);

A cláusula real ORDER BYé muito mais complexa, mas isto é suficiente para ilustrar o meu problema.

Parece-me que isso deveria ser possível com apenas uma varredura t1, mas EXPLAIN QUERY PLANdiz que esta consulta verifica t1duas vezes:

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  |--SCAN t1
|  `--USE TEMP B-TREE FOR ORDER BY
`--SCALAR SUBQUERY 2
   `--SCAN t1

Se eu unir as duas subconsultas em vez de escrevê-las como colunas de resultados, o plano de consulta será diferente, mas ainda terá duas varreduras de t1:

QUERY PLAN
|--CO-ROUTINE (subquery-1)
|  |--SCAN t1
|  `--USE TEMP B-TREE FOR ORDER BY
|--MATERIALIZE (subquery-2)
|  `--SCAN t1
|--SCAN (subquery-1)
`--SCAN (subquery-2)

Imperativamente, eu esperaria que esta consulta fosse algo como este pseudocódigo:

var top_row = {i: NULL, c1: 0};
var count = 0;
for each {i, c1, c2} in t1:
  if c1 % 2 == 0:
    if c1 > top_row.c1:
      top_row = {i, c1};
    if c2 % 2 == 0:
      count = count + 1;
return {top_row.i, count};

Como posso fazer com que o planejador de consultas veja que isso precisa apenas de uma verificação?

Atualização, 09/05/2024: tentei a consulta proposta por Charlieface . De acordo com EXPLAIN QUERY PLAN, isso faz com que o planejador de consultas use apenas uma varredura de t1...

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  |--SCAN t1
|  |  `--USE TEMP B-TREE FOR ORDER BY
|  `--SCAN (subquery-4)
`--SCAN t

... mas é executado significativamente mais lentamente do que minha SELECT (...), (...)consulta original: testando ambas as consultas no SQLite REPL com .timer on, descobri que (para minha tabela de exemplo t1com 5 milhões de linhas de dados aleatórios) minha consulta original tem um tempo médio de execução de 2,22 segundos com desvio padrão amostral de 0,03 s e esta consulta proposta tem média de 5,57 s com st. dev. 0,23 seg.

Algo que minha intuição imperativa não me sugeriu, mas que o estudo dos planos de consulta "USE TEMP B-TREE FOR ORDER BY" sugeriu foi adicionar um índice em t1(c1). Isso realmente acelera minha consulta original, fazendo com que a média seja de 1,09 s com st. dev. 0,02 seg. No entanto, para minha surpresa, o índice aparentemente faz com que a consulta de Charlieface demore muito mais ou possivelmente até mesmo a torna ininterrupta - interrompi-a depois de esperar 108 segundos e interrompi-a depois de tentar novamente por 32 s, e então o fiz. não tente novamente.

A consulta de Charlieface responde literalmente à minha pergunta, reduzindo o número de varreduras t1para um, mas seu baixo desempenho prático em relação à minha consulta original me faz relutante em aceitá-la como resposta. Espero que isso não seja "mover demais as traves da baliza". Eu marquei isso como query-performance, então o desempenho fez parte da minha pergunta desde o início.

Atualização, 09/05/2024 nº 2: Com CREATE INDEX t1_c1 ON t1(c1), o plano de consulta para minha consulta original tornou-se

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  `--SCAN t1 USING COVERING INDEX t1_c1
`--SCALAR SUBQUERY 2
   `--SCAN t1

e o plano de consulta para a consulta de Charlieface tornou-se

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  `--SCAN t1 USING INDEX t1_c1
|  `--SCAN (subquery-4)
`--SCAN t

Com CREATE INDEX index_per_comment660077_339327 ON t1 (c1 DESC) WHERE (c1 % 2 = 0)o comentário do Charlieface (SQLite não suporta INCLUDE), os planos de consulta tornam-se, respectivamente,

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  `--SCAN t1 USING COVERING INDEX index_per_comment660077_339327
`--SCALAR SUBQUERY 2
   `--SCAN t1 USING INDEX index_per_comment660077_339327

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  `--SCAN t1 USING INDEX index_per_comment660077_339327
|  `--SCAN (subquery-4)
`--SCAN t

Temos uma tabela de índice de pesquisa, fornecida por um CMS, que tem o formato:

CREATE TABLE `craft_searchindex` (
  `elementId` int NOT NULL,
  `attribute` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `fieldId` int NOT NULL,
  `locale` char(12) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `keywords` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`elementId`,`attribute`,`fieldId`,`locale`),
  FULLTEXT KEY `craft_searchindex_keywords_idx` (`keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

Como parte da funcionalidade de pesquisa fornecida pelo CMS, é formada a seguinte consulta:

SELECT * FROM `craft_searchindex` WHERE (`keywords` LIKE '% tom %') AND `locale` = 'en_au' AND `elementId` IN (<ids>)

onde idsé uma lista filtrada de elementIds com base em uma consulta anterior.

O que observamos é que quando atingimos um determinado número de itens naquela INcláusula, o desempenho cai e o tempo de execução da consulta torna-se constante, conforme mostrado em um gráfico coletado antes da migração para o InnoDB (tempo de execução do eixo y em milissegundos, x- número do eixo de itens na INcláusula, série min/média/máx):

A última linha de uma EXPLAIN ANALYZEconsulta mostra o seguinte para aproximadamente 33.500 itens:

-> Index range scan on craft_searchindex using PRIMARY over (elementId = 1) OR (elementId = 128) OR (33500 more)  (cost=55355 rows=234514) (actual time=0.0376..537 rows=469028 loops=1)

e para aproximadamente 34.000 itens:

-> Table scan on craft_searchindex  (cost=335885 rows=3.27e+6) (actual time=0.0303..3237 rows=3.72e+6 loops=1)

Minha interpretação disso (que está de acordo com o gráfico) é que:

• até certo limite, o índice de chave primária é verificado (um por um, fornecendo o crescimento linear) e
• além desse limite, a tabela completa é sempre verificada (fornecendo o valor constante)

Minha pergunta é, dado que há uma diferença significativa entre o tempo de execução imediatamente abaixo desse limite e o tempo acima dele, por que o planejador de consultas opta por abandonar a verificação do índice[*], e algo pode ser feito a respeito (com a restrição que a preparação da consulta é realizada pelo CMS, portanto, na maior parte fora do nosso controle)?

Ingressando em uma mesa temporária

Embora não seja uma solução para nós, dadas as restrições das consultas tratadas pelo CMS, segui a sugestão de @Akina de usar uma tabela temporária indexada. Isso altera a consulta de (efetivamente):

SET SESSION group_concat_max_len = 1 << 19;
PREPARE ids_stmt FROM 'SELECT GROUP_CONCAT(`id`)
INTO @ids_clause
FROM (
  SELECT `elements`.`id`
  FROM `craft_elements` `elements`
  JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
  JOIN `craft_content` `content` ON content.elementId = elements.id
  JOIN `craft_users` `users` ON users.id = elements.id
  WHERE ((elements_i18n.locale = ?) AND (content.locale = ?)) AND (elements.archived = 0)
) AS `ids`';
SET @l = 'en_au';
EXECUTE ids_stmt USING @l, @l;  
DEALLOCATE PREPARE ids_stmt;

SET @search_query = CONCAT("SELECT `craft_searchindex`.* FROM `craft_searchindex` WHERE (`keywords` LIKE '% tom %') AND `locale` = 'en_au' AND `elementId` IN (", @ids_clause, ')');

PREPARE search_stmt FROM @search_query;
EXECUTE search_stmt;
DEALLOCATE PREPARE search_stmt;

para:

PREPARE ids_stmt FROM 'CREATE TEMPORARY TABLE ids_table
(PRIMARY KEY(`id`))
SELECT `elements`.`id`
FROM `craft_elements` `elements`
JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `craft_content` `content` ON content.elementId = elements.id
JOIN `craft_users` `users` ON users.id = elements.id
WHERE ((elements_i18n.locale = ?) AND (content.locale = ?)) AND (elements.archived = 0)
LIMIT 1';
SET @l = 'en_au';
EXECUTE ids_stmt USING @l, @l; 
DEALLOCATE PREPARE ids_stmt;

SELECT `craft_searchindex`.*
FROM `craft_searchindex`
JOIN `ids_table` ON `craft_searchindex`.`elementId` = `ids_table`.`id`
WHERE (`keywords` LIKE '% tom %') AND `locale` = 'en_au';

e isso mantém o crescimento linear do tempo de execução bem além do limite anterior.

Curiosamente (e talvez porque a tabela temporária tenha apenas uma coluna) a eliminação da chave primária não afeta o tempo de execução.

Abaixo está um gráfico comparando estes (os tempos agora estão em segundos):

[*]: A partir de alguns cálculos breves, continuar a varredura do índice em vez de realizar uma varredura completa da tabela ainda estaria abaixo do tempo de execução da varredura completa da tabela até que o número de itens na INcláusula excedesse aproximadamente 200.000

Tenho uma pergunta simples, mas não consegui encontrar uma resposta/explicação direta. Desculpe se estiver duplicado

Quero construir uma tabela de pontuação para um pequeno jogo de perguntas e respostas onde os usuários recebem perguntas e podem estar certos ou errados. Então, acho que tenho 2 opções, ou uma tabela com 1 linha = 1 resposta do usuário, assim:

Ou uma tabela com 1 linha = 1 pontuação global do usuário:

Tenho praticamente nenhuma experiência com otimização de banco de dados/sql, então não sei qual é o mais eficiente.

Aqui está meu processo de pensamento:

Primeira opção : Melhor(?)/Mais rápido(?) para adicionar/atualizar uma pontuação porque não me importo com o estado atual da tabela, só preciso inserir uma linha Mas, para obter a pontuação global de um usuário, eu precisaria de um pesado (?) consulta como

SELECT SUM(certo), SUM(errado) WHERE userId = x;

Como eu gostaria de mostrar a pontuação global de um usuário em cada página/solicitação, sinto que essa não é a escolha inteligente. Além disso, como 1 linha = 1 resposta do usuário, a tabela pode ficar muito grande.

Primeira opção : Mais lento(?) para adicionar uma pontuação, pois teria que atualizar uma linha existente. Significa SELECT e depois UPDATE, em cada resposta. Não é particularmente o meu caso, mas esta opção é menos flexível, pois - ao contrário da primeira opção - não poderei armazenar qual pergunta foi respondida correta/incorretamente. Mas, com isso posso obter a pontuação total de um usuário sem uma consulta pesada. Seria melhor se uma consulta UPDATE pudesse retornar a linha resultante, então não preciso UPDATE e depois SELECT para mostrar.

Por último, se você tiver a gentileza de dedicar algum tempo para me responder, poderia explicar brevemente um ou outro é tecnicamente melhor e como posso testá-lo (existe algum tipo de ferramenta/procedimento)

Obrigado