Olivier Pons's questions

A consulta a seguir procura todas as cidades que estão entre 5 e 10 quilômetros de uma latitude/logitude codificada (=outra cidade, na verdade). Eu tenho 37010 cidades.

Estou usando o Symfony 2 que "cria" uma consulta através do Doctrine. Esta consulta é sinalizada pelo meu servidor MariaDB como "não usando índices". Não sei o que está acontecendo, porque: (1) Aqui está a consulta:

SELECT
    v0_.id AS id0,
    v0_.nom AS nom1,
    v0_.url AS url2,
    v0_.cp AS cp3,
    v0_.insee AS insee4,
    ROUND(
        6371 *
        ACOS(COS(RADIANS(50.58907000)) *
        COS(RADIANS(v0_.lat)) *
        COS(RADIANS(v0_.lng) -
        RADIANS(3.16710500)) +
        SIN(RADIANS(50.58907000)) *
        SIN(RADIANS(v0_.lat))), 2
    ) AS sclr5
    FROM ville v0_
    HAVING sclr5 > 4 AND sclr5 <= 10
    ORDER BY sclr5 ASC LIMIT 20 OFFSET 0;

Aqui está a hora:

# User@Host: x[x] @ localhost []
# Thread_id: 1514  Schema: mydatabase  QC_hit: No
# Query_time: 0.071503  Lock_time: 0.000137  Rows_sent: 20  Rows_examined: 37030

E aqui está a tabela:

MariaDB [mydatabase]> desc ville;
+-----------------+---------------------+------+-----+---------+----------------+
| Field           | Type                | Null | Key | Default | Extra          |
+-----------------+---------------------+------+-----+---------+----------------+
| id              | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| id_origine      | bigint(20) unsigned | YES  | MUL | NULL    |                |
| date_v_creation | datetime            | YES  | MUL | NULL    |                |
| date_v_debut    | datetime            | YES  | MUL | NULL    |                |
| date_v_fin      | datetime            | YES  | MUL | NULL    |                |
| article         | varchar(4)          | YES  |     |         |                |
| nom             | varchar(150)        | NO   | MUL |         |                |
| url             | varchar(150)        | NO   | MUL |         |                |
| cp              | varchar(10)         | NO   | MUL | NULL    |                |
| insee           | varchar(10)         | NO   |     |         |                |
| id_region       | bigint(20) unsigned | NO   | MUL | NULL    |                |
| id_departement  | bigint(20) unsigned | NO   | MUL | NULL    |                |
| lat             | decimal(15,8)       | NO   | MUL | NULL    |                |
| lng             | decimal(15,8)       | NO   |     | NULL    |                |
| sound           | varchar(252)        | YES  | MUL | NULL    |                |
+-----------------+---------------------+------+-----+---------+----------------+
15 rows in set (0.01 sec)

MariaDB [mydatabase]>

Se eu fizer uma explicação sobre esta consulta, recebo:

+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+
|    1 | SIMPLE      | v0_   | ALL  | NULL          | NULL | NULL    | NULL | 36510 | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+

1 carreira em conjunto (0,00 seg)

Eu tenho 37.010 cidades e MariaBN me diz Rows_examined: 37030. Eu não entendo isso. De qualquer forma, como você o otimizaria?

Tenho certeza de que muitos aplicativos, aplicativos críticos, bancos e assim por diante fazem isso diariamente.

A ideia por trás de tudo isso é:

• todas as linhas devem ter um histórico
• todos os links devem permanecer coerentes
• deve ser fácil fazer solicitações para obter colunas "atuais"
• os clientes que compraram coisas obsoletas ainda devem ver o que compraram, mesmo que este produto não faça mais parte do catálogo

e assim por diante.

Aqui está o que eu quero fazer e explicarei os problemas que estou enfrentando.

Todas as minhas tabelas terão essas colunas:

• id
• id_origin
• date of creation
• start date of validity
• start end of validity

E aqui estão as ideias para operações CRUD:

• create = inserir nova linha com id_origin= id, date of creation=now, start date of validity=now, end date of validity=null (= significa que é o registro ativo atual)
• atualizar =
  • read = ler todos os registros com end date of validity==null
  • atualize o registro "atual" end date of validity=nulo com end date of validity=agora
  • crie um novo com os novos valores e end date of validity=null (= significa que é o registro ativo atual)
• delete = atualize o registro "atual" end date of validity=null com end date of validity=agora

Então aqui está o meu problema: com associações muitos-para-muitos. Vamos dar um exemplo com valores:

• Tabela A (id = 1, id_origin = 1, início=agora, fim=nulo)
• Tabela A_B (início=agora, fim=nulo, id_A = 1, id_B = 48)
• Tabela B (id = 48, id_origin = 48, início=agora, fim=nulo)

Agora eu quero atualizar a tabela A, registro id=1

• Eu marco id de registro=1 com end=agora

• Eu insiro um novo valor na tabela A e... caramba, perdi minha relação A_B , a menos que eu duplique a relação também... isso terminaria em uma tabela:

• Tabela A (id = 1, id_origin = 1, início=agora, fim=agora+8mn)

• Tabela A (id = 2, id_origin = 1, start=now+8mn, end=null)
• Tabela A_B (início=agora, fim=nulo, id_A = 1, id_B = 48)
• Tabela A_B (início=agora, fim=nulo, id_A = 2, id_B = 48)
• Tabela B (id = 48, id_origin = 48, início=agora, fim=nulo)

E... bem, eu tenho outro problema: a relação A_B: devo marcar (id_A = 1, id_B = 48) como obsoleto ou não (A - id=1 é obsoleto, mas não B - 48)?

Como lidar com isso?

Tenho que projetar isso em grande escala: produtos, parceiros e assim por diante.

Qual é a sua experiência sobre isso? Como você faria (como você fez)?

-- Editar

Eu encontrei este artigo muito interessante , mas não lida adequadamente com "obsolescência em cascata" (= o que estou perguntando na verdade)

Aqui , há uma amostra de um gatilho com a cláusula "INSTEAD OF UPDATE":

CREATE TRIGGER [dbo].[Test_PTA_Table_Update_trg]
-- ALTER TRIGGER [dbo].[Test_PTA_Table_Update_trg]
   ON  [dbo].[Test_PTA_Table]
   INSTEAD OF UPDATE
AS
   SET NOCOUNT ON
   DECLARE @key int
   SET @key = (SELECT TestTablePK FROM Inserted)

   UPDATE Test_PTA_Table
      SET DateEnd = GetDate(), DateReplaced = GetDate()
      WHERE TestTablePK = @key

   INSERT INTO dbo.Test_PTA_Table
   (TestTableText, DateCreated, DateEffective, OperatorCode, DateReplaced)
   (SELECT TestTableText, GetDate(), GetDate(), OperatorCode, NULL
FROM Inserted)

Estou tentando portar o princípio para o MySQL:

DELIMITER ;;

DROP TRIGGER IF EXISTS attribut_trigger_update_before;

CREATE TRIGGER attribut_trigger_update_before
BEFORE UPDATE ON attribut
  FOR EACH ROW BEGIN
    INSERT INTO attribut SET 
      id_origine      = OLD.id_origine,
      date_v_creation = OLD.date_v_creation,
      date_v_debut    = OLD.date_v_debut,
      date_v_fin      = OLD(),
      importance      = OLD.importance,
      description     = OLD.description,
      valeur          = OLD.valeur,
      abbreviation    = OLD.abbreviation;
  END;;

DELIMITER ;;

Mas não funciona.

mysql> update produit set importance=3;
ERROR 1442 (HY000): Can't update table 'produit' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql> 

Alguma idéia de como fazer isso?