Início / coding / Perguntas / 79590075
Accepted
Jimin Lim
Asked: 2025-04-24 15:58:44 +0800 CST

Como aplicar o índice espacial na minha situação sem usar 'or' e 'union all'

  • 772

Tenho refatorado usando o índice espacial em Haversine,

Eu queria escrever uma instrução SQL que verificasse postagens em um raio de 10 km com o valor de ponto (0,90) para obter informações sem distância, mas confirmei que o índice não é usado se 'ou' for usado.

Então, tentei combinar as duas consultas SELECT usando UNION ALL , mas o desempenho permaneceu tão lento quanto antes, tornando-o ineficaz. Testei isso com 50.000 linhas de dados e não houve melhora perceptível — mesmo com o índice espacial sendo usado corretamente neste caso.

Como último recurso, pensei em separar as postagens sem informações de distância das postagens com informações de distância para separar a API, mas a interface mudou e agora o front não consegue refatorar, então quero excluí-la o máximo possível. Obrigado 🙏

select b1_0.id,
   b1_0.title,
   b1_0.item_time,
   b1_0.item_price,
   b1_0.created_date,
   b1_0.chat_count,
   b1_0.scrap_count,
   st_distance_sphere(b1_0.location, ST_GeomFromText('POINT(127.0 37.5)', 4326)),
   b1_0.address,
   b1_0.board_state,
   (select i1_0.stored_file_name
    from image i1_0
    where i1_0.board_id = b1_0.id
    order by i1_0.id LIMIT 1)
from board b1_0
where b1_0.board_type = 'SELL'
  and (ST_Contains(ST_Buffer(ST_GeomFromText('POINT(127.0 37.5)', 4326), 0.1), b1_0.location)
           OR (latitude = 90.0 AND longitude = 0.0))
order by b1_0.created_date desc
limit 0, 8;
sql

1 respostas

  1. Best Answer

    Um truque que você pode usar aqui seria remover o ORna WHEREcláusula e, em vez disso, expressar sua consulta como uma união da seguinte maneira:

    select
    b1_0.id,
    b1_0.title,
    b1_0.item_time,
    b1_0.item_price,
    b1_0.created_date,
    b1_0.chat_count,
    b1_0.scrap_count,
    st_distance_sphere(b1_0.location, ST_GeomFromText('POINT(127.0 37.5)', 4326)),
    b1_0.address,
    b1_0.board_state,
    (select i1_0.stored_file_name
     from image i1_0
     where i1_0.board_id = b1_0.id
     order by i1_0.id)
from board b1_0
where b1_0.board_type = 'SELL'
    and (ST_Contains(ST_Buffer(ST_GeomFromText('POINT(127.0 37.5)', 4326), 0.1), b1_0.location)
union all
select
    b1_0.id,
    b1_0.title,
    b1_0.item_time,
    b1_0.item_price,
    b1_0.created_date,
    b1_0.chat_count,
    b1_0.scrap_count,
    st_distance_sphere(b1_0.location, ST_GeomFromText('POINT(127.0 37.5)', 4326)),
    b1_0.address,
    b1_0.board_state,
    (select i1_0.stored_file_name
     from image i1_0
     where i1_0.board_id = b1_0.id
     order by i1_0.id)
from board b1_0
where b1_0.board_type = 'SELL' and
      latitude = 90.0 and
      longitude = 0.0
order by created_date desc
limit 0, 8;

    Para otimizar a consulta de união acima, podemos criar uma estratégia de indexação para ambas as metades da união.

    -- para a consulta principal

    CREATE INDEX idx1 ON board (board_type, location);

    -- para a consulta inferior

    CREATE INDEX idx2 ON board (board_type, latitude, longitude);
    • 1

relate perguntas