Estou projetando uma tabela postgres para registrar os intervalos contidos em uma programação semanal. Ele manteria agendas para várias empresas, e um conjunto de dados de exemplo simples pode ser semelhante a:

business_id  interval
-----------  -----------------------------------
1            Sunday   10:00:00 – Sunday 14:00:00
1            Sunday   22:00:00 – Monday 02:00:00
1            Friday   11:00:00 – Friday 16:00:00
1            Saturday 15:00:00 – Sunday 01:00:00

Observe que os intervalos podem cruzar os limites entre os dias.

Uma empresa não deve ter intervalos sobrepostos e gostaria de projetar a tabela de uma forma que me permitisse impor isso.

Eu estava pensando em mapear esses valores de dia da semana + hora do dia para os números correspondentes de segundos desde o início da semana, armazenando intervalos como int4rangee usando uma restrição de exclusão para proibir a sobreposição de intervalos inteiros, mas isso não seria adequado intervalos de endereços que envolvem o final da semana.

Existe uma boa maneira de modelar esse tipo de dados cíclicos e proibir sobreposições?

Dada uma tabela (postgres),

CREATE TABLE links (
    id bigint NOT NULL,
    url text NOT NULL,
    owner_id bigint NOT NULL
);

Eu gostaria de permitir que várias linhas tenham o mesmo urlse e somente se elas também tiverem o mesmo owner_id.

Ou seja, isso seria permitido:

 id | url | owner_id
----+-----+---------
  7 | foo |        1
  8 | bar |        1
  9 | bar |        1

mas isso não seria:

 id | url | owner_id
----+-----+---------
  7 | foo |        1
  8 | bar |        1
  9 | bar |        2

Existe alguma maneira de impor tal restrição?

Eu sei que a ordem das colunas em um índice composto é importante se você quiser consultar por um subconjunto das colunas indexadas, mas se sua consulta especificar valores para todas as colunas indexadas, há algum benefício de desempenho em ter uma coluna de alta cardinalidade antes de uma coluna de baixa cardinalidade? Lembro-me vagamente de ler algo que sugeria que era esse o caso, porque reduz o conjunto de resultados mais rapidamente, mas não consigo encontrar nada para apoiar isso agora.

Estou usando MySQL com InnoDB. O InnoDB usa índices clusterizados, o que pode ser relevante para minha pergunta, mas acho que só faz isso para chaves primárias, o que meu índice não é. A tabela fica mais ou menos assim:

CREATE TABLE `my_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ref_a_id` int(10) unsigned NOT NULL,
  `ref_b_id` int(10) unsigned NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_my_table_on_ref_a_ref_b_is_active` (`ref_a_id`, `ref_b_id`, `is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=2818259 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Em relação à minha pergunta, imagine ref_a_idtem uma cardinalidade maior que ref_b_id.

Dada esta consulta,

SELECT a.*
FROM a
LEFT JOIN b ON b.a_id = a.id
LEFT JOIN c ON c.a_id = a.id
HAVING COUNT(DISTINCT b.id) < COUNT(DISTINCT c.id)

se b for uma tabela significativamente maior que c, haverá implicações de desempenho na ordem em que faço as uniões? Li em algum lugar que a regra geral é unir o menor ao maior, mas isso pode estar se referindo a INNER JOINs.

Percebo que os resultados dependerão da ordem das junções externas em muitos cenários, mas em um como o acima, em que a ordem não importa para a veracidade, isso provavelmente afetará o desempenho?

Executando MySQL com InnoDB:

Tenho uma SELECT wide_table.*consulta que desejo refinar, SELECT wide_table.idpois é tudo o que o código de chamada precisa. Testando, descobri que o tempo de execução com *é mais rápido do que com id(embora o tempo para transferir o resultado pela rede seja mais rápido com a versão "refinada").

Por que isso seria o caso?

A consulta (com nomes alterados para proteger os inocentes) é:

SELECT
    `things`.*
FROM
    `things`
WHERE
    `things`.`active` = 1
        AND (owner_id IS NOT NULL
        AND owner_id > 0)
        AND ((`things`.`status` IN (0 , 1)
        OR `things`.`status` IS NULL))
        AND (date < '2015-07-11 00:00:00');

Há um índice composto em activee date, que está sendo usado em ambas as versões.

Para referência, a saída de SHOW CREATE TABLE(com colunas irrelevantes omitidas):

CREATE TABLE `things` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) DEFAULT '0',
  `date` datetime DEFAULT NULL,
  `owner_id` int(11) DEFAULT '0',
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_things_on_active_and_date` (`active`,`date`),
  KEY `index_things_on_date` (`date`),
  KEY `index_things_on_owner_id` (`owner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1862 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Limites

Depois de brincar um pouco com isso, descobri que a comparação é afetada pelo limite imposto à consulta. Com limites grandes, >200, a *versão relata um tempo de execução mais rápido. À medida que o limite diminui <200, a idversão ganha vantagem. Ainda não tenho certeza do que fazer com isso...

EXPLIQUE

A execução EXPLAINem ambas as versões da consulta gera uma saída idêntica, com select_type: SIMPLEe key: index_things_on_active_and_date.

Estou trabalhando com um aplicativo Rails apoiado por um banco de dados MySQL. Um dos modelos usados ​​com mais frequência é apoiado por uma tabela com mais de 200.000.000 de linhas (e crescendo). Há algum tempo, sabemos que a configuração não é escalável.

O recurso em que estou trabalhando no momento exige que eu adicione algumas novas colunas a essa tabela, bem como um índice composto. Meu colega de trabalho fez um teste em sua máquina de desenvolvimento local e demorou cerca de uma hora por coluna (nem sequer testei a adição do índice ainda).

Estamos pensando em adotar uma abordagem diferente, construir uma nova tabela com as colunas necessárias, despejar os dados da tabela antiga na nova e, em vez disso, apontar o modelo Rails para a nova tabela. Parece uma solução de curto prazo para um grande problema iminente, mas estou curioso para saber se alguém tem uma ideia de como lidar com esse problema.

Também falamos sobre o arquivamento dos registros antigos da tabela. É uma tabela que é muito escrita e lida, mas apenas com registros recentes (o aplicativo provavelmente nunca precisa fazer nada com registros com mais de um mês).

Minha tabela atualmente possui uma coluna booleana, vamos chamá-la de coluna "importante", refletindo se um registro é urgente ou não. Desejo fazer a transição para o uso de uma coluna varchar que reflita a urgência de um registro como um dos poucos valores possíveis, como "normal", "urgente", "extremo". O nome da coluna precisa mudar para, então estou planejando fazer algo assim: crie uma nova coluna chamada "urgência" para manter o valor normal/urgente/extremo e migre seletivamente a data booleana existente para a nova coluna.

Esta é uma boa abordagem e há alguma pegadinha que devo observar?