Otimizar SQL Sugestões

[shakall]

Galera venho novamente pedir ajuda de vocês, com a tabela abaixo possuo algo em torno de 1milhão de registros, e agora preciso gerar um sql que me retorne uma posição dentro de uma margem de valores, criei o seguinte sql.

SELECT DISTINCT DS_POSICAO
FROM POSICAO_GPS
WHERE CD_EMPRESA=17 
AND DS_POSICAO IS NOT NULL AND 
(NR_LAT>=-12.92493 AND NR_LAT<=-12.91493) AND (NR_LOG>=-49.43828 AND NR_LOG<=-49.40828)
ORDER BY DT_POSICAO DESC
LIMIT 1

Quando executo este sql, caso tenha alguma posição nesta margem de valores ela é retornada rapidamento, e no explain ele utiliza os index e passa em apenas 1 linha, isso ta funcionando muito bem. O problema é se eu passar uma margem que não tenha nenhum registro, simplesmente ele demora 300segundos em media para não retornar nada, e ao testar no explain ele diz que passou em 11 linhas.

"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"SIMPLE";"POSICAO_GPS";"index";"Ref_91,Index 10,Index 9,Index 11";"IDX-DTPOSICAO-SQVEICULO-CDEMPRESA";"14";NULL;"11";"Using where; Using temporary"

Criei varios index para ver se melhora o desempenho mas o problema continua se não existir uma posição na margem passada ele demora 300segundos para responder, se tem em menos de 0.200 segundos é respondido. não sei mais o que fazer para resolver isso.

Obrigado!

CREATE TABLE `posicao_gps` (
    `SQ_POSICAO_GPS` VARCHAR(36) NOT NULL,
    `SQ_VEICULO` INT(11) NULL DEFAULT NULL,
    `CD_EMPRESA` INT(6) NULL DEFAULT NULL,
    `SQ_DISPOSITIVO_VEICULO` BIGINT(19) NULL DEFAULT NULL,
    `DT_POSICAO` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `NR_ANTENA_RASTREAMENTO` BIGINT(20) NULL DEFAULT NULL,
    `NR_LAT` VARCHAR(50) NOT NULL,
    `NR_LOG` VARCHAR(50) NOT NULL,
    `DS_POSICAO` VARCHAR(400) NULL DEFAULT NULL,
    `ID_IGNICAO` VARCHAR(1) NULL DEFAULT NULL,
    `VL_VELOCIDADE` FLOAT NULL DEFAULT NULL,
    `STATUS` VARCHAR(1) NOT NULL DEFAULT 'V',
    PRIMARY KEY (`SQ_POSICAO_GPS`),
    INDEX `Ref_90` (`SQ_VEICULO`),
    INDEX `Ref_91` (`CD_EMPRESA`),
    INDEX `IDX-DTPOSICAO-SQVEICULO-CDEMPRESA` (`DT_POSICAO`, `SQ_VEICULO`, `CD_EMPRESA`),
    INDEX `SQ_DISPOSITIVO_VEICULO` (`SQ_DISPOSITIVO_VEICULO`),
    INDEX `index 7` (`SQ_VEICULO`, `DT_POSICAO`),
    INDEX `status` (`STATUS`),
    INDEX `Index 10` (`CD_EMPRESA`, `NR_LAT`, `NR_LOG`),
    INDEX `Index 9` (`NR_LAT`),
    INDEX `Index 11` (`NR_LOG`),
    CONSTRAINT `FK_posicao_gps_dispositivo_veiculo` FOREIGN KEY (`SQ_DISPOSITIVO_VEICULO`) REFERENCES `dispositivo_veiculo` (`SQ_DISPOSITIVO_VEICULO`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `Ref_90` FOREIGN KEY (`SQ_VEICULO`) REFERENCES `veiculo` (`SQ_VEICULO`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `Ref_91` FOREIGN KEY (`CD_EMPRESA`) REFERENCES `empresa` (`CD_EMPRESA`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

[Denis Courcy]

Oi 'shakall'

Vamos por partes:

Modifique seu sql para que fique assim:

SELECT DISTINCT DS_POSICAO
FROM POSICAO_GPS
WHERE CD_EMPRESA=17 
AND DS_POSICAO IS NOT NULL AND 
(NR_LAT BETWEEN "-12.92493" AND "-12.91493") AND (NR_LOG BETWEEN "-49.43828" AND "-49.40828")
ORDER BY DT_POSICAO DESC
LIMIT 1

Crie um indice por NR_LAT e NR_LOG

CREATE INDEX `nomedoseuindice`  ON `posicao_gps` (`NR_LAT`, `NR_LOG`)

Faça testes e reporte resultado.

[shakall]

Olá Denis, obrigado pela ajuda.

Fiz o que você disse, mudei para between, mas tive que tirar as "" pois com elas nada era retornado, retirando ele encontra os registros.

Adicionei o index que você comentou mas no explain ele não faz uso, ele utilza outro index da empresa, segue abaixo os resultados, vou gerar um script com a tabela e os dados, assim fica mais facil para você me ajudar.

explain SELECT DISTINCT DS_POSICAO
FROM POSICAO_GPS
WHERE CD_EMPRESA=17 
AND DS_POSICAO IS NOT NULL AND 
(NR_LAT BETWEEN -12.92493 AND -12.91493) AND (NR_LOG BETWEEN -49.43828 AND -49.40828)
ORDER BY DT_POSICAO DESC
LIMIT 1

"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"SIMPLE";"POSICAO_GPS";"ref";"Ref_91,localidade";"Ref_91";"5";"const";"58724";"Using where; Using temporary; Using filesort"


    INDEX `Ref_91` (`CD_EMPRESA`),

[shakall]

Segue o script da tabela.

PosicaoGPS

Obrigado pela ajuda!

[shakall]

Galera se alguém puder me ajudar na otimização da busca deste select, continuo com o problema, já tentei diversas formas de melhorar mas sem sucesso.

Segue abaixo o script com a tabela, dados e o select para facilitar.

Muito Obrigado.

Script

[Denis Courcy]

Oi 'shakall' ,

Há algum tempo venho trabalhando em uma solução para seu problema.

Depois de muito rodar montei o índice abaixo que deu um bom tempo de resposta.

explain SELECT DISTINCT DS_POSICAO
FROM POSICAO_GPS USE INDEX(EMPRESA_LAT_LOG)
WHERE CD_EMPRESA=20 AND  
(NR_LAT BETWEEN -12.92493 AND -12.91493) AND (NR_LOG BETWEEN -49.43828 AND -49.40828) AND
DS_POSICAO IS NOT NULL 
ORDER BY DT_POSICAO DESC;

CREATE INDEX EMPRESA_LAT_LOG
ON posicao_gps (`CD_EMPRESA` ASC,`NR_LAT` ASC,`NR_LOG` ASC,`DT_POSICAO` DESC,`DS_POSICAO`(2) ASC);

Tenta aí.

[shakall]

Oi 'shakall' ,

Há algum tempo venho trabalhando em uma solução para seu problema.

Depois de muito rodar montei o índice abaixo que deu um bom tempo de resposta.

explain SELECT DISTINCT DS_POSICAO
FROM POSICAO_GPS USE INDEX(EMPRESA_LAT_LOG)
WHERE CD_EMPRESA=20 AND  
(NR_LAT BETWEEN -12.92493 AND -12.91493) AND (NR_LOG BETWEEN -49.43828 AND -49.40828) AND
DS_POSICAO IS NOT NULL 
ORDER BY DT_POSICAO DESC;

CREATE INDEX EMPRESA_LAT_LOG
ON posicao_gps (`CD_EMPRESA` ASC,`NR_LAT` ASC,`NR_LOG` ASC,`DT_POSICAO` DESC,`DS_POSICAO`(2) ASC);

Tenta aí.

Putz cara nem sei como agradecer, muito obrigado pela ajuda. Cara testei aqui esta index mas infelizmente não deu certo, olha o exemplo abaixo ele levou 2 minutos para ser executado, depois tem o explain.

SELECT DISTINCT DS_POSICAO FROM POSICAO_GPS USE INDEX(EMPRESA_LAT_LOG) WHERE CD_EMPRESA=12 AND (NR_LAT BETWEEN -15.020000 AND -14.980000) AND (NR_LOG BETWEEN -41.020000 AND -40.980000) AND DS_POSICAO IS NOT NULL ORDER BY DT_POSICAO DESC LIMIT 1;
/* 0 rows affected, 1 rows found. Duration for 1 query: 122,991 sec. */

Explain

"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"SIMPLE";"POSICAO_GPS";"range";"EMPRESA_LAT_LOG";"EMPRESA_LAT_LOG";"15";NULL;"19656";"Using where; Using temporary; Using filesort"